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

Creating a UNIX_TIMESTAMP() Function in SQL Server

MySQL's UNIX_TIMESTAMP() function is a useful tool for converting dates to a Unix timestamp, which represents the number of seconds that have elapsed since January 1, 1970. While SQL Server does not have a built-in equivalent of this function, it is possible to create a custom function to achieve similar functionality.

For Dates After 1970

If you are not concerned about dates before 1970 or millisecond precision, you can use the following simple formula:

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

This formula calculates the number of seconds between the specified DateField and January 1, 1970. It closely matches the output of MySQL's UNIX_TIMESTAMP() function.

For Millisecond Precision

If you need millisecond precision, you can use the DATEDIFF_BIG() function in SQL Server 2016/13.x and later. Here's the modified formula:

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

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