SQL Server: Convert a Unix epoch time string to a human readable date time field

Body

Searching though a table in a SQL Server database, I found the date field I was looking for.  To my dismay, the field's value was a string in Unix epoch time - a value representing the number of seconds since January 1, 1970.

I decided to convert the string to a date/time format that I was more used to seeing.  After much wailing and gnashing of teeth, I found the format I needed.

Let's explore this step by step:

The original query consisted of:

SELECT FNAME, LNAME, JOIN_DATE
FROM MEMBERS
WHERE FNAME = 'SUE' AND
LNAME = 'JONES';
 

This was a nice, relatively pithy SQL query and it yielded:

FNAME    LNAME    JOIN_DATE
SUE        JONES    0001516993312125

I did a quick web search and learned that SQL Server's DATEADD() function was the ticket to what I needed.  However,  my first attempt failed miserably, as did my second and third attempts.  However, these failures were instructive, as they led to the solution.

1. The JOIN_DATE column is a string, so it must be converted to a numeric value.

DATEADD(SS, CONVERT(BIGINT, JOIN_DATE), '19700101')

2. The value '0001516993312125' is too long - we must truncate the leading zeroes and the last 3 digits:

DATEADD(SS, CONVERT(BIGINT, SUBSTRING(JOIN_DATE, 4,10)), '19700101')

3. At last I was getting where I wanted to be:

SELECT FNAME, LNAME, DATEADD(SS, CONVERT(BIGINT, SUBSTRING(JOIN_DATE, 4,10)), '19700101')
FROM MEMBERS
WHERE WHERE FNAME = 'SUE' AND
LNAME = 'JONES';
 

yielded:

FNAME    LNAME     2 N/A
SUE        JONES     2018-01-26 19:01:52

4. The time value was now human-readable, but I didn't like the column name, 2/NA.  There was also another issue - the time was wrong!  This was puzzling until I realized that the result failed to account for the time zone in which I live (GMT -5, if you must know.)  I decided to fix the column name and represent the value in local time:

SELECT FNAME, LNAME, , DATEADD(HH, -5, (DATEADD(SS, CONVERT(bigint, SUBSTRING(c2.OIN_DATE, 4,10)), '19700101'))) AS 'Date_Joined',
FROM MEMBERS
WHERE WHERE FNAME = 'SUE' AND
LNAME = 'JONES';

FNAME    LNAME     DATE_JOINED
SUE        JONES     2018-01-26 14:01:52

The query wasn't pretty, and it had been a slog, but I had what I needed.  In celebration, I broke out the ginger tea and biscuits.