Convert a Legacy DB2 based numeric format to smalldatetime

I mentioned recently that I've been working on an application that uses both SQL Server and DB2 databases. So far so good: I've been able to use the DB2 database as a linked server with a few limitations. I've also been able to access both the SQL Server and DB2 databases using generated CSLA based business object classes written in VB.Net.

That's been great but I've run into a few snags along the way. The DB2 database is hosted on a iSeries computer. The iSeries is an evolutionary step from the AS/400, which evolved from the System/38, which evolved from the System/3, which was introduced in the 1970's. The RPG code in my client's application has a similar long history. As a consequence of this history some of the data fields have unusual coding.

This week and for the next three weeks I'll show some data type conversion UDFs that have been used to translate from the formats on the iSeries to data types that SQL Server handles.

The first storage format stores a date as a numeric (6,0). The six digits represent the month, day, and year. So to represent January 1, 2001 the value is 10101. Since the date is stored in a numeric column, there's no leading zero. The number was entered on the green screen as it was stored without slashes or other editing.

Translating this format to a smalldatetime took a little work and is the job of this week's UDF: udf_DT_FromDecimal. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER FUNCTION dbo.udf_DT_FromDecimal (

    @DecimalDate numeric (6,0) -- numeric date mmddyy

) RETURNS smalldatetime
/* 
* Converts from one of the numeric representation of a date used 
* on the iSeries (f.k.a AS/400 f.k.a System/38 f.k.a System 3)  
* into a SQL datetime.  This format of the date is mmddyy as
* a six digit numeric.  So 10101 is Jan 1, 2001 and 12/31/99
* is December 31st, 1999.
* Must be alert to the possibility of bad data and return NULL
* 
* Example:
SELECT dbo.udf_DT_FromDecimal (CONVERT(numeric(6,0), 10101))
     , dbo.udf_DT_FromDecimal (CONVERT(numeric(6,0), 123199))
     , dbo.udf_DT_FromDecimal (CONVERT(numeric(6,0),  23000))
*
* © 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 #22
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
******************************************************************/
AS BEGIN

	DECLARE @WrkDT smalldatetime
          , @CharDT varchar(12)
          , @Yr int
          , @Mn int
          , @Dy int

SELECT @Yr = CONVERT(VARCHAR(2), CONVERT(int,@DecimalDate) % 100)
     , @Mn = CONVERT(VARCHAR(2)
               , (CONVERT(int,@DecimalDate)  
                   - CONVERT(int,@DecimalDate) % 10000) / 10000)
     , @Dy = CONVERT(VARCHAR(2)
               , (CONVERT(int,@DecimalDate) % 10000 
                        - CONVERT(int,@DecimalDate) % 100) / 100)
                        
SET @YR = @Yr + CASE WHEN @YR > 50 THEN 1900 ELSE 2000 END
    IF @Mn < 0 or @Mn > 12
       or @Dy < 0 or @Dy > 31 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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXEC on dbo.udf_DT_FromDecimal TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

The function works by splitting the year, month, and day from the input and then combining them to make the smalldatetime. Here's an example of running the function:

SELECT dbo.udf_DT_FromDecimal(CONVERT(numeric(6,0),10101)) [Jan 1, 01]
     , dbo.udf_DT_FromDecimal(CONVERT(numeric(6,0),123199)) [Dec 31, 99]
     , dbo.udf_DT_FromDecimal(CONVERT(numeric(6,0), 23000)) [Bad date]
GO

(Results - reformatted with some names shortened)
Jan 1, 01              Dec 31, 99           Bad date             
---------------------- -------------------- ---------------------
2001-01-01 00:00:00    1999-12-31 00:00:00  NULL

Next week we'll convert back from a datetime to the MMDDYY format used on the iSeries so that we can update the linked tables.

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