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

Convert UTC Date to MM/DD/YYYY HH MM SS

luckyinc
luckyinc asked
on
Medium Priority
866 Views
Last Modified: 2012-05-04
I currently have integer values in the database examples:

1077558217
1077642495
1077641785

That need to be converted to logical date and time.  
Any suggested functions for SQL Server 2000 or SQL Server 7?

Thanks
LuckyInc
Comment
Watch Question

Commented:
What is the baseline for the integers?

Author

Commented:
This number is the number of SECONDS since 1970, 01, 01

It is stored into the database as an int (4)

Commented:
If you know that 1077558217 is for ex: 01/01/2004
then 1077642495 is the date:
1077642495 - 1077558217 + cast('01/01/2004' as datetime)

Author

Commented:
Lets say the field name is

mydate

Criteria would be
mydate-1075400710 + Cast('01/29/2004' as datetime)
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
SELECT DATEADD(SECOND, yourIntValue, '1970-01-01')

Commented:
e.g. print dateadd(ss, 1077641785, '01/01/1970')

Author

Commented:
How do I implement THIS FORMULA INTO SQL SERVER

MyFieldIntegerValue/86400+DATE(1970,1,1)
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
That will just give you whole days.  If that's what you want, you can do this:


SELECT DATEADD(DAY, yourIntValue / 86400, '1970-01-01')


Note, then, that the time on the final result will always be midnight, because "/" will drop any remainder.
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
This is not correct.  
If you test with real int representing UTC, such as 1104178047, the above function will report 2004-12-27, but the correct answer is 2005-03-02.  I will repost quesiton.

Author

Commented:
I noticed that after I accepted the answer...where are you reposting the question?

Commented:
db/mssql

Commented:
Repost is Question Title: UTC int Convert to standard DateTime how?
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.