MS SQL Numeric to datetime format conversion

I have a numeric date imported from AS400 in this format: 104125.  It's actually the 125th day of 2004.

How do I convert this in MS SQL to a datetime format?

Thanks
cmaxinAsked:
Who is Participating?
 
vc01778Connect With a Mentor Commented:
Perhaps your datatype is 'varchar'.  Then:

create table t1(x varchar(6))

insert into t1 values('104125')

select dateadd(day,  cast(x as int) % 1000 - 1, dateadd(year, cast(x as int) /1000, '1900/1/1') ) from t1

VC
0
 
vc01778Commented:
Assuming that last three digits reperesent a day and the first three 1900 + years:

select dateadd(day,  your_column % 1000 - 1, dateadd(year, your_column/1000, '1900/1/1') ) from your_table

E.g.:

select dateadd(day, 104125 % 1000 - 1, dateadd(year, 104125/1000, '1900/1/1') )

gives:

5/4/2004 12:00:00 AM


VC
0
 
Anthony PerkinsCommented:
Declare @AS400Date integer
Set @AS400Date = 104125
Select DateAdd(Day, @AS400Date % 1000, DateAdd(Year, @AS400Date / 1000, '1900-01-01'))
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Anthony PerkinsCommented:
Oops!
0
 
cmaxinAuthor Commented:
Thanks for the response!.  That seems to work if your data type is integer, but mine is numeric.
0
 
vc01778Commented:
create table t1(x numeric(6,0))

insert into t1 values(104125)

select dateadd(day,  x % 1000 - 1, dateadd(year, x/1000, '1900/1/1') ) from t1

5/4/2004 12:00:00 AM


????

VC
0
 
cmaxinAuthor Commented:
OK.  I changed my data type from numeric to integer and this worked for me:

Declare @AS400Date integer
Set @AS400Date = 104125
Select DateAdd(Day, @AS400Date % 1000, DateAdd(Year, @AS400Date / 1000, '1900-01-01'))

Thank you very much!!
0
 
Anthony PerkinsCommented:
Please do not award me any points, I merely duplicate (inadvertently) vc01778's solution.
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.