Translate a date to a legacy month format MMYY

This week's UDF, udf_DT_ToDecimalMMYY, is the compliment of last week's UDF, udf_DT_FromDecimalMMYY. These functions convert to and from a legacy date format that specifies a month and year.

Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   FUNCTION dbo.udf_DT_ToDecimalMMYY (

    @Date datetime -- SQL datetime to convert

) RETURNS numeric (4,0) -- date stored in the AS/400 format MMYY
/* 
* Converts from a SQL datetime to the numeric version in the form
* MMYY as stored on the iSeries (AS/400)
* 
* Example:
SELECT dbo.udf_DT_ToDecimalMMYY ('2001-01-01') [1/1/01]
     , dbo.udf_DT_ToDecimalMMYY ('1999-12-31') [12/31/99]
     , dbo.udf_DT_ToDecimalMMYY ('2002-2-28')  [2/28/02]
*
* © Copyright 2004 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published in the T-SQL UDF of the Week Newsletter Vol 2 #25
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
******************************************************************/
AS BEGIN

	RETURN       (Month(@Date) * 100)
               + (YEAR(@Date) - CASE WHEN YEAR(@Date) > 1999 
                                     THEN 2000 
                                     ELSE 1900 END)

END


GO

GRANT EXEC on dbo.udf_DT_ToDecimalMMYY TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Let's test out the function:

SELECT dbo.udf_DT_ToDecimalMMYY ('2001-01-01') [1/1/01]
     , dbo.udf_DT_ToDecimalMMYY ('1999-12-31') [12/31/99]
     , dbo.udf_DT_ToDecimalMMYY ('2002-2-28')  [2/28/02]

GO

(Results)
1/1/01/ 12/31/99 2/28/02 
------- -------- ------- 
    101     1299     202 

As far as I can tell, this format was used because it was easy to implement on the green screen and compact to store as well.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick