select unix timestamp as yyyy-mm-dd

[Andy Dufresne]

I have a bigint column in a table that contains a unix timestamp. I can convert that timestamp to a "human" date using the DATEADD() function:

SELECT DATEADD(s, lastaccessdate, '19700101 00:00:00:000') AS lastaccessdate
FROM MyTable

Result:

lastaccessdate
2013-02-04 16:05:51.000

How can I get SQL Server to show just the date portion (and drop the time)? I want:

lastaccessdate
2013-02-04

Thanks.

[Erland Sommarskog]

Post by Andy Dufresne
I have a bigint column in a table that contains a unix timestamp. I can
SELECT DATEADD(s, lastaccessdate, '19700101 00:00:00:000') AS
lastaccessdate FROM MyTable
lastaccessdate
2013-02-04 16:05:51.000

First a nit-pick: SQL Server does not show anything. SQL Server is a server
application and does not have any interface.

But if you only want the date, the eaiest is simply to convert
the value to the date data type:

SELECT cast (DATEADD(s, lastaccessdate, '19700101') AS date)
AS lastaccessdate
FROM MyTable

This assumes that you are using SQL 2008 or later.

[Ken]

Post by Andy Dufresne
I have a bigint column in a table that contains a unix timestamp. I can
SELECT DATEADD(s, lastaccessdate, '19700101 00:00:00:000') AS
lastaccessdate
FROM MyTable
lastaccessdate
2013-02-04 16:05:51.000
lastaccessdate
2013-02-04
Thanks.

SELECT CONVERT(VARCHAR(10),DATEADD(s, lastaccessdate, '19700101
00:00:00:000'),120) AS lastaccessdate
FROM MyTable