Convert TimeStamp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Convert TimeStamp

Author

Topic

squab
Starting Member

15 Posts

Posted - 2004-05-10 : 12:06:02

Hi,

I try to convert Timestamp (time in seconds since 01/01/1970) to a date (datetime format). How, in T-SQL, I can do that ?

Thanks in advance,

SqUaB

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-05-10 : 12:17:15

To convert it to a date time you will have to start with a min date (which I have called StartDate in my example)

select dateadd(ss, datediff(ss, '1970-01-01', getdate()), StartDate)

Duane.

squab
Starting Member

15 Posts

Posted - 2004-05-10 : 12:40:00

Thanks for you help Duane, but I don't understand your example. Could you give me a real example. For example, how you convert this timestamp : 1083891600(seconds) in datetime ?

Best Regards,

SqUaB

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-05-10 : 12:50:23

To be able to do this you need to start with a date that represents 0 Seconds.
Which date represents 0 Seconds?

In my example I will say 1900-01-01 represents 0 Seconds.

select dateadd(ss, 1083891600, '1900-01-01')

So the result of this is 1083891600 seconds since 1900-01-01 00:00:00.000

Hope this helps.

Duane.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-10 : 12:52:08

select dateadd(ss, 1083891600,'1970-01-01')

Duane - He did give a start date...