How to convert Datetime to Unix timestamp in T-SQL

🎈 🎈 🎈


    SELECT DATEDIFF(SECOND, '1970-01-01 00:00:00', '2024-08-29 02:28:16') AS UnixTimestamp
    FROM YourTable;

Output:

------------------ | UnixTimestamp | ------------------ | 1724898496 |

Example only. There may be multiple ways to perform this operation.

Code Explanation

In this T-SQL query, the DATEDIFF function is used to calculate the difference between two dates. The first argument, SECOND, specifies that the difference should be measured in seconds. The second argument is the Unix epoch start date ('1970-01-01 00:00:00'), and the third argument is the date you want to convert ('2024-08-29 02:28:16').
The function returns the number of seconds between these two dates, effectively converting your date to a Unix timestamp. The resulting column, UnixTimestamp, contains the Unix timestamp value for each date in your table.

Convert Unix timestamp to Date in T-SQL

Other useful T-SQL date functions

Function	Description	Example
`GETDATE()`	Returns the current date and time of the system in the format 'YYYY-MM-DD HH:MM:SS.mmm'.	`GETDATE()` returns 2024-08-28 12:34:56.789
`GETUTCDATE()`	Returns the current UTC date and time in the format 'YYYY-MM-DD HH:MM:SS.mmm'.	`GETUTCDATE()` returns 2024-08-28 16:34:56.789
`DATEADD()`	Adds a specified number of time units (e.g., days, months, years) to a date.	`DATEADD(DAY, 5, '2024-08-28')` returns 2024-09-02
`DATEDIFF()`	Returns the difference between two dates as an integer in specified time units (e.g., days, months, years).	`DATEDIFF(DAY, '2024-08-28', '2024-09-02')` returns 5
`FORMAT()`	Formats a date/time value according to the specified format string.	`FORMAT(GETDATE(), 'dd/MM/yyyy')` returns 28/08/2024
`EOMONTH()`	Returns the last day of the month that contains the specified date, with an optional offset.	`EOMONTH('2024-08-28')` returns 2024-08-31
`YEAR()`	Returns the year part of a date.	`YEAR('2024-08-28')` returns 2024
`MONTH()`	Returns the month part of a date.	`MONTH('2024-08-28')` returns 8
`DAY()`	Returns the day part of a date.	`DAY('2024-08-28')` returns 28
`SWITCHOFFSET()`	Adjusts a datetimeoffset value by a specified time zone offset.	`SWITCHOFFSET('2024-08-28 12:34:56.789 +00:00', '-08:00')` returns 2024-08-28 04:34:56.789 -08:00
`SYSDATETIME()`	Returns the current date and time as a datetime2 value, with higher precision than GETDATE().	`SYSDATETIME()` returns 2024-08-28 12:34:56.7891234

Current Unix Timestamp SECONDS SINCE JAN 01 1970

Current UTC Time

2025-08-20