We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

MS SQL Numeric to datetime format conversion

cmaxin
cmaxin asked
on
Medium Priority
1,231 Views
Last Modified: 2010-05-19
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
Comment
Watch Question

Commented:
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
CERTIFIED EXPERT
Top Expert 2012

Commented:
Declare @AS400Date integer
Set @AS400Date = 104125
Select DateAdd(Day, @AS400Date % 1000, DateAdd(Year, @AS400Date / 1000, '1900-01-01'))
CERTIFIED EXPERT
Top Expert 2012

Commented:
Oops!

Author

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

Commented:
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
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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!!
CERTIFIED EXPERT
Top Expert 2012

Commented:
Please do not award me any points, I merely duplicate (inadvertently) vc01778's solution.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.