How to Create a UNIX_TIMESTAMP() Equivalent in SQL Server?

Creating a UNIX_TIMESTAMP() Equivalent in SQL Server

MySQL's UNIX_TIMESTAMP() function allows users to convert a date-time value into the number of seconds since the Unix epoch (1970-01-01 00:00:00 UTC). To achieve similar functionality in SQL Server 2008, we can employ a simple calculation:

SELECT DATEDIFF(s, '1970-01-01 00:00:00', DateField);

This calculation subtracts the number of seconds between the Unix epoch and the specified DateField. For instance, to convert the date '2022-12-31 12:00:00' to a Unix timestamp, we would execute:

SELECT DATEDIFF(s, '1970-01-01 00:00:00', '2023-01-01 12:00:00');

This yields the result: 1672531200, which represents the number of seconds since the Unix epoch.

However, if you require millisecond precision (available in SQL Server 2016/13.x and later), you can use the following calculation:

SELECT DATEDIFF_BIG(ms, '1970-01-01 00:00:00', DateField);

This calculation returns the number of milliseconds since the Unix epoch with greater accuracy.

The above is the detailed content of How to Create a UNIX_TIMESTAMP() Equivalent in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!