asked on 4/2/2004

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

Microsoft SQL Server

Last Comment

Anthony Perkins

7/14/2004

vc01778

4/2/2004

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

Anthony Perkins

4/2/2004

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

Anthony Perkins

4/2/2004

Oops!

cmaxin

4/2/2004

ASKER

Thanks for the response!. That seems to work if your data type is integer, but mine is numeric.

vc01778

4/2/2004

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

ASKER CERTIFIED SOLUTION

vc01778

4/2/2004

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

cmaxin

4/2/2004

ASKER

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

Anthony Perkins

7/14/2004

Please do not award me any points, I merely duplicate (inadvertently) vc01778's solution.