Convert Datetime to Unix timestamp

Question

In Microsoft SQL Server 2012 or above, is it possible to convert a datetime value to Unix time stamp in a single select statement? If so, how can it be done?

The answer to your question is yes. What's your actual question? — Michael McGriff, Dec 24 '15 at 16:31

khaled4vokalz · Accepted Answer · 2021-04-17T05:09:28.627

47

As Peter Halasz mentions in T-SQL DateTime to Unix Timestamp:

Converting a datetime to unix timestamp is easy, but involves error prone typing the following:
@timestamp=DATEDIFF(second,{d '1970-01-01'},@datetime)
Where @datetime is the datetime value you want to convert. The {d ‘yyyy-mm-dd’} notation is an ODBC escape sequence.

The function:
CREATE FUNCTION UNIX_TIMESTAMP (
@ctimestamp datetime
)
RETURNS integer
AS
BEGIN
  /* Function body */
  declare @return integer
   
  SELECT @return = DATEDIFF(SECOND,{d '1970-01-01'}, @ctimestamp)
   
  return @return
END

Try it out now like below @O A:

SELECT UNIX_TIMESTAMP(GETDATE());

edited Apr 17 '21 at 05:09

answered Dec 24 '15 at 16:27

khaled4vokalz

876
9
13

4

Any reason not to simplify to: select DATEDIFF(SECOND, '1970-01-01', @ctimestamp)? or select DATEDIFF(SECOND, CONVERT( DATETIME, '1970-01-01', 121 ), @ctimestamp) Date localization? – onupdatecascade Dec 24 '15 at 17:29
1

Thanks Khaled and onupdatecascade, both solutions work. I agree, your one is simpler and doesn't require the creation of a function. – O A Dec 24 '15 at 18:21
1

@onupdatecascade while Ousman is trying to use something on a regular basis don't you think that writing the whole thing everytime is poorer than using it as a function? – khaled4vokalz Dec 25 '15 at 08:59
@OusmanAhmad if you think that it's working then mark it as an answer for the future users who are facing such problems.... Thank You – khaled4vokalz Dec 25 '15 at 08:59
2

Reusability is a good practice in many places but scalar UDFs (specifically that kind) present some serious performance issues in SQL Server, and sometimes it is actually better to include an expression like this inline instead. – onupdatecascade Dec 28 '15 at 15:08
2

A colleague once related a simple way to tell: if your function requires BEGIN ... END then it can't be inlined automatically by the optimizer, and that should be a red flag in terms of performance – onupdatecascade Dec 28 '15 at 15:08

score 3 · Answer 2 · answered Sep 16 '21 at 11:43

maybe this answer will help someone... If you have a problem when you try to convert datetime using datediff function to number of seconds (mssql message: The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.) then use:

select cast(datediff(d,'1970-01-01',@d1) as bigint)*86400+datediff(s,dateadd(day, datediff(day, 0, @d1), 0),@d1)

if you have ms sql server 2016+ use DATEDIFF_BIG function

score 0 · Answer 3 · answered Jun 07 '22 at 11:05

my function with localization

CREATE FUNCTION UNIX_TIMESTAMP(@ctimestamp datetime)
RETURNS integer
AS
begin
  return DATEDIFF(second,'1970-01-01',GETUTCDATE())+datediff(second,GETDATE(),@ctimestamp)
end

then i use value in js code

new Date(unixSec*1000)

Convert Datetime to Unix timestamp

3 Answers3

Linked