Convert a datetime to a legacy iSeries/DB2 format.

The last two weeks have been devoted to a legacy date format that was used on a DB2 database running on a iSeries computer running RPG applications. This week we convert a similar legacy date format. This one takes the form MMYY and is used to represent a month.

SQL Server doesn't have a data type that represents only the month and year portions of a date. Of course, the numeric format for the date could have been retained when the data was moved from DB2 to SQL Server, but the advantages of the datetime data type would be just as lost on SQL Server as it is on the iSeries. I decided to translate the MMYY into a smalldatetime and use the first of the month for the date. That way the dates would sort and the DATEDIFF(m, d1 ,d2) function would work.

This week's function is udf_DT_FromDecimalMMYY. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  FUNCTION dbo.udf_DT_FromDecimalMMYY (

    @DecimalDate numeric (4,0) -- numeric date MMYY

) RETURNS smalldatetime

  WITH SCHEMABINDING
/* 
* Converts from the numeric representation of a MMYY date used on 
* the AS/400 into a SQL datetime.  The first of the month is assumed.
* Must be alert to the possibility of bad data and return NULL
* 
* Example:
SELECT dbo.udf_DT_FromDecimalMMYY (CONVERT(numeric(4,0), 101))
     , dbo.udf_DT_FromDecimalMMYY (CONVERT(numeric(4,0), 1299))
     , dbo.udf_DT_FromDecimalMMYY (CONVERT(numeric(4,0), 1000))
select dbo.udf_DT_FromDecimalMMYY (CONVERT(numeric(4,0), 688))
*
* © 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 #24 6/1/04
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
******************************************************************/
AS BEGIN

DECLARE @WrkDT smalldatetime
      , @CharDT varchar(12)
      , @Yr int
      , @Mn int
      , @Dy int
SELECT @Yr = CONVERT(int,@DecimalDate) % 100
     , @Mn = (CONVERT(int,@DecimalDate)  
                       - (CONVERT(int,@DecimalDate) % 100))
             / 100
     , @Dy = 1
SET @YR = @Yr + CASE WHEN @YR > 50 THEN 1900 ELSE 2000 END
IF @Mn < 1 or @Mn > 12 RETURN NULL

SET @CharDT = CONVERT(CHAR(4), @YR) + '-'
            + RIGHT('00' + CONVERT(VARCHAR(2), @Mn), 2) + '-'
            + RIGHT('00' + CONVERT(VARCHAR(2), @Dy), 2)

IF ISDATE(@CharDT)=0 RETURN NULL
RETURN CONVERT(smalldatetime, @CharDT, 120)

END

GO

GRANT EXEC on dbo.udf_DT_FromDecimalMMYY TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Let's test out the function:

SELECT dbo.udf_DT_FromDecimalMMYY (CONVERT(numeric(4,0), 101)) [Jan '01]
     , dbo.udf_DT_FromDecimalMMYY (CONVERT(numeric(4,0), 1299)) [Dec '99]
     , dbo.udf_DT_FromDecimalMMYY (CONVERT(numeric(4,0), 1000)) [Oct '00]
select dbo.udf_DT_FromDecimalMMYY (CONVERT(numeric(4,0), 688)) [Jun '88]

GO


(Results)
Jan '01              Dec '99              Oct '00             
-------------------- -------------------- --------------------
2001-01-01 00:00:00  1999-12-01 00:00:00  2000-10-01 00:00:00


Jun '88              
-------------------- 
1988-06-01 00:00:00

I wouldn't have chosen this as a way to store a month but I guess this was the easiest format for data entry on the green screen. Going back to the legacy days, I suppose it was pretty compact to store as well. It shouldn't have taken more than two bytes to store the 4 digits. While we don't worry about database size as much as we did when disks weren't so large it can still be a concern and sometimes it's necessary to compact data using techniques like MMYY.

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