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

Effortlessly Convert Unix Timestamps to Human-Readable Dates in SQL Server

Many developers encounter difficulties converting Unix timestamps to a user-friendly format within Microsoft SQL Server. This guide provides a straightforward solution for this common conversion task.

Understanding the Challenge:

Unix timestamps represent the number of seconds elapsed since January 1, 1970, at 00:00:00 UTC. SQL Server, however, requires dates in a more readable format, such as 'YYYY-MM-DD HH:MM:SS.mmm'.

The Simple Solution:

The DATEADD function offers a concise way to perform this conversion:

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

Here, '19700101' signifies January 1, 1970. The DATEADD function adds the specified number of seconds (your Unix timestamp) to this base date, producing a DATETIME value.

Illustrative Example:

Let's convert the Unix timestamp 1291388960:

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

The result will be '2010-12-03 15:09:20.000', the equivalent SQL Server date and time.

Important Notes:

• The output of DATEADD is a DATETIME data type. Use CAST or CONVERT for other date/time formats if needed.
• This method seamlessly handles multiple columns containing Unix timestamps; simply apply the conversion to each column individually.

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