• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 840
  • 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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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