Solved

# SQL Server Format DB2 serial into Date

Posted on 2011-04-27
584 Views
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'?
0
Question by:glenn_r

LVL 9

Expert Comment

It should be a maximum of 6 digits else your result will be indeterministic.
0

LVL 9

Accepted Solution

when you query it from AS400, format your date using Convert/CAST
0

Author Comment

So whats the function syntax gona look like? So are you suggesting I just change the 12 to a 6 in the statement?
0

LVL 75

Expert Comment

>>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

LVL 75

Assisted Solution

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

## Featured Post

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…