How Can I Convert Unix Timestamps to Human-Readable Dates in SQL Server?

Converting Unix Timestamps to Human-Readable Dates within SQL Server

Challenge:

Importing data from an Excel spreadsheet containing Unix timestamps necessitates their conversion to a human-readable date and time format within SQL Server. This involves transforming three columns populated with Unix timestamps.

Resolution:

The SQL Server DATEADD function provides the solution. This function adds a specified time interval (measured in seconds) to a starting date. For Unix timestamps, the base date is January 1st, 1970.

The DATEADD function's syntax for this conversion is:

<code class="language-sql">DATEADD(ss, unix_timestamp, '19700101')</code>

Here, unix_timestamp represents the Unix timestamp value, and '19700101' is the base date in YYYYMMDD format. The result is a DATETIME data type. Further formatting using CAST or CONVERT can refine the output.

Illustrative Example:

Consider a table named 'Customers' with a column 'EpochTimestamp' holding Unix timestamps. To update the table with converted timestamps in a new column, use this query:

<code class="language-sql">UPDATE Customers
SET SQLServerTimestamp = DATEADD(ss, EpochTimestamp, '19700101');</code>

This query replaces the original Unix timestamps with their SQL Server DATETIME equivalents in the 'SQLServerTimestamp' column. You can then use CAST or CONVERT to format the date as needed for display or further processing.

The above is the detailed content of How Can I Convert Unix Timestamps to Human-Readable Dates in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!