Sorry, for solving the problem in the wrong direction. My lame excuse is that it is a lot more common that people ask about how to convert a Unix timestamp to the SQL Server data types and the other way round.
Here is a new function:
CREATE OR ALTER FUNCTION LocalTimeToUnixTime(@local datetime, @timezone varchar(50)) RETURNS bigint AS
BEGIN
DECLARE @do datetimeoffset(3) = @local AT TIME ZONE @timezone
SELECT @do = switchoffset(@do, '+00:00')
RETURN datediff_big(second, '19700101', convert(datetime2(3), @do))
END
go
SELECT dbo.LocalTimeToUnixTime('2021-02-12 04:10:00.000', 'Central European Standard Time')
This time I made the time zone a parameter so I could test in my local time.
As for the data types, SQL Server has both datetime
and datetime2
due to legacy. datetime
has a range from 1754-01-01 to 9999-12-31, and a resolution of 3.33 ms. A datetime
value takes up eight bytes. datetime2
has a range from 0001-01-01 (but always according to the Gregorian calendar) to 9999-12-31, and you can select from a resolution of full seconds (datetime2(0)
) down to 100 ns (datetime2(7)
.) Stupidly, if you say datetime2
this is the same as datetime2(7)
, but you very rarely have need for a resolution of more than 1 ms, since this is what you get from the function sysdatetime(). (getdate() returns datetime
, so here you only get 3.33 ms.) datetime(0)
to datetime(2)
take up six bytes, whereas datetime2(3)
and datetime2(4)
take up four bytes.
One advantage with datetime2
is that date strings on the format YYYY-MM-DD always work. This is not the case with datetime.