Convert eg. 2012-05-25 to seconds since January 1 1970

sql-server date time timespan epoch
netigger picture netigger · Oct 16, 2012 · Viewed 15.6k times · Source

I have a database where I save time as time() from php, which is seconds since 1 jan 1970.

Is there any way I can convert, for example 2012-12-12 to seconds since 1 jan 1970?

I want to do like:

SELECT * 
FROM Table 
WHERE date > '2012-11-30' AND date < '2012-12-30'

Is this even possible?

(I would like to do it without any php date())

Answer

Tim Lehner picture Tim Lehner · Oct 16, 2012

DATEDIFF is going to be your friend here:

select datediff(s, '1970-01-01', '2012-12-12') as SecondsSinceEpoch

Note that because datediff returns an int, the maximum datetime you can compare (using seconds) with Jan 1st, 1970 is 2038-01-19 03:14:07.

Related questions

Difference between datetime and timestamp in sqlserver?

What is the difference between Timestamp and Datetime SQL Server? I thought Both formats are capable of storing date + time. Then, Where the difference is lying between them? But Timestamp is not capable of storing date, time information. Still Whats …

Read More
How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?

Read More
Best approach to remove time part of datetime in SQL Server

Which method provides the best performance when removing the time portion from a datetime field in SQL Server? a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) or b) select cast(convert(char(11), getdate(), 113) as datetime) The second method does send a …

Read More