• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 832
  • Last Modified:

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

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
0
luckyinc
Asked:
luckyinc
  • 4
  • 3
  • 3
  • +2
1 Solution
 
millsoftCommented:
What is the baseline for the integers?
0
 
luckyincAuthor Commented:
This number is the number of SECONDS since 1970, 01, 01

It is stored into the database as an int (4)
0
 
ATAHACCommented:
If you know that 1077558217 is for ex: 01/01/2004
then 1077642495 is the date:
1077642495 - 1077558217 + cast('01/01/2004' as datetime)
0
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!

 
luckyincAuthor Commented:
Lets say the field name is

mydate

Criteria would be
mydate-1075400710 + Cast('01/29/2004' as datetime)
0
 
Scott PletcherSenior DBACommented:
SELECT DATEADD(SECOND, yourIntValue, '1970-01-01')
0
 
millsoftCommented:
e.g. print dateadd(ss, 1077641785, '01/01/1970')
0
 
luckyincAuthor Commented:
How do I implement THIS FORMULA INTO SQL SERVER

MyFieldIntegerValue/86400+DATE(1970,1,1)
0
 
Scott PletcherSenior DBACommented:
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.
0
 
Scott PletcherSenior DBACommented:
The earlier version:

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

would give you a full time, including hours, minutes and seconds, which better matches your original request of:
"Convert UTC Date to MM/DD/YYYY HH MM SS"
0
 
donschliCommented:
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.
0
 
luckyincAuthor Commented:
I noticed that after I accepted the answer...where are you reposting the question?
0
 
donschliCommented:
db/mssql
0
 
donschliCommented:
Repost is Question Title: UTC int Convert to standard DateTime how?
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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