MSsql (sql server) convert Datetime / Timestamp into Unix timestamp (bigint)

select CURRENT_TIMESTAMP;

select CONVERT(BIGINT, DATEDIFF(S, '19700101', '20380119')) + CONVERT(BIGINT, DATEDIFF(S, '20380119', CURRENT_TIMESTAMP)) ;

select DateAdd(SECOND, CONVERT(BIGINT, DATEDIFF(S, '19700101', '20380119')) + CONVERT(BIGINT, DATEDIFF(S, '20380119', CURRENT_TIMESTAMP)), '1970-01-01T00:00:00.000') AS TIME_MS;

convert from unix time to sql server datetime:

SELECT DATEADD(s, mycolumn / 1000, '19700101') from mytable

 

It would seem that we could use "ms" instead of the "s" parameter and avoid division by 1000, since the data are stored in milliseconds in mycolumn. However, that created an overflow condition. My only guess is that the dataadd function uses integer math internally instead of bigint.

Next up, how to convert the other direction:

SELECT cast(DATEDIFF(s, '19700101', cast('2012-10-10 14:05:00.000' as datetime)) as bigint) * 1000 from mytable

 

You'll want to replace '2012-10-10 14:05:00.000' with your own date/time string. Again we have this overflow problem if we try to get milliseconds directly in datediff, so we have to multiply by 1000.

This is all well and fine, but my data was not in the GMT timezone. So I additionally need to convert through to my timezone (CST).

SELECT cast(DATEDIFF(s, '19700101', dateadd(hh, 5, cast('2012-10-10 14:05:55.000' as datetime))) as bigint) * 1000 from mytable

 

As before, replace '2012-10-10 14:05:55.000' with your own date/time string.

SQL SERVER – Few Useful DateTime Functions to Find Specific Dates

Recently I have recieved email from Vivek Jamwal, which contains many useful SQL Server Date functions.

----TodaySELECT GETDATE() 'Today'----YesterdaySELECT DATEADD(d,-1,GETDATE()) 'Yesterday'----First Day of Current WeekSELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)'First Day of Current Week'----Last Day of Current WeekSELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)'Last Day of Current Week'----First Day of Last WeekSELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0)'First Day of Last Week'----Last Day of Last WeekSELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6)'Last Day of Last Week'----First Day of Current MonthSELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)'First Day of Current Month'----Last Day of Current MonthSELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))'Last Day of Current Month'----First Day of Last MonthSELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))'First Day of Last Month'----Last Day of Last MonthSELECTDATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))'Last Day of Last Month'----First Day of Current YearSELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)'First Day of Current Year'----Last Day of Current YearSELECTDATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))'Last Day of Current Year'----First Day of Last YearSELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))'First Day of Last Year'----Last Day of Last YearSELECTDATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))'Last Day of Last Year'

ResultSet:

Today
———————–
2008-08-29 21:54:58.967

Yesterday
———————–
2008-08-28 21:54:58.967

First Day of Current Week
————————-
2008-08-25 00:00:00.000

Last Day of Current Week
————————
2008-08-31 00:00:00.000

First Day of Last Week
———————–
2008-08-18 00:00:00.000

Last Day of Last Week
———————–
2008-08-24 00:00:00.000

First Day of Current Month
————————–
2008-08-01 00:00:00.000

Last Day of Current Month
————————-
2008-08-31 23:59:59.997

First Day of Last Month
———————–
2008-07-01 00:00:00.000

Last Day of Last Month
———————–
2008-07-31 23:59:59.997

First Day of Current Year
————————-
2008-01-01 00:00:00.000

Last Day of Current Year
————————
2008-12-31 23:59:59.997

First Day of Last Year
———————–
2007-01-01 00:00:00.000

Last Day of Last Year
———————–
2007-12-31 23:59:59.997

Reference : Pinal Dave (http://blog.SQLAuthority.com), Vivek Jamwal