SQL Server Format DB2 serial into Date

I have a linked server to AS400/DB2. I'm querying a date which is stored in a 6 digit zoned decimal field. When queried in SQL Server I get 5 or 6 digit dates back in the following format. Upto 2009 is 5 digits and from 2010 and up is 6 digits.

ymmdd - 91105
yymmdd - 101105

I tried using SELECT convert(datetime, '161023', 12) but only works for the 6 digit format.

Can someone suggest a good, efficient, and easy to understand way of converting these dates using a SELECT to the format '10/23/2016'?
glenn_rAsked:
Who is Participating?
 
radcaesarCommented:
when you query it from AS400, format your date using Convert/CAST
0
 
radcaesarCommented:
It should be a maximum of 6 digits else your result will be indeterministic.
0
 
glenn_rAuthor Commented:
So whats the function syntax gona look like? So are you suggesting I just change the 12 to a 6 in the statement?
0
 
Anthony PerkinsCommented:
>>Can someone suggest a good, efficient, and easy to understand way of converting these dates <<
It is pretty simple:
SELECT CONVERT(datetime, RIGHT('0' + YourDate, 6), 12)

This is how I tested it:
DECLARE @YourDate varchar(6)
SET @YourDate = '101105'

SELECT CONVERT(datetime, RIGHT('0' + @YourDate, 6), 12)
0
 
Anthony PerkinsCommented:
I forgot the output:

DECLARE @YourDate varchar(6)
SET @YourDate = '101105'
SELECT CONVERT(datetime, RIGHT('0' + @YourDate, 6), 12)

SET @YourDate = '91105'
SELECT CONVERT(datetime, RIGHT('0' + @YourDate, 6), 12)

Output:

-----------------------
2010-11-05 00:00:00.000

-----------------------
2009-11-05 00:00:00.000
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.