Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 609
  • Last Modified:

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'?
0
glenn_r
Asked:
glenn_r
  • 2
  • 2
2 Solutions
 
radcaesarCommented:
It should be a maximum of 6 digits else your result will be indeterministic.
0
 
radcaesarCommented:
when you query it from AS400, format your date using Convert/CAST
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now