glenn_r
asked on
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'?
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'?
radcaesar
It should be a maximum of 6 digits else your result will be indeterministic.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So whats the function syntax gona look like? So are you suggesting I just change the 12 to a 6 in the statement?
>>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)
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)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.