How to Replicate MySQL's UNIX_TIMESTAMP() Function in SQL Server?

Querying UNIX_TIMESTAMP in SQL Server

MySQL's UNIX_TIMESTAMP() function provides a concise method for converting a datetime value to its corresponding UNIX timestamp. However, in SQL Server, implementing a similar functionality can require some ingenuity. For systems running SQL Server 2008 and above, here's how you can overcome this limitation:

Solution 1: Ignoring Pre-1970 Dates

For applications that don't handle dates prior to 1970, you can leverage SQL Server's DATEDIFF function as follows:

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

This expression subtracts the '1970-01-01 00:00:00' baseline from the specified DateField, effectively achieving a similar result to MySQL's UNIX_TIMESTAMP().

Solution 2: Precision for SQL Server 2016 and Later

For finer precision down to milliseconds, available in SQL Server 2016 and later, utilize the DATEDIFF_BIG function:

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

This variation provides millisecond-level granularity in the UNIX timestamp conversion.

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