Getting year datepart from unix date MS SQL

by Tom   Last Updated May 23, 2018 12:06 PM

On my database, the dates are stored as days elapsed since/before 01/01/1970 which I understand to be a unix date format. eg. today is 23rd May 2018 and the value is 17674

I'm trying to run a select statement against a column in a date table to show only the year.

If I run this: datepart(year, c.cko_date)

I get dates that are in the 1940s (they should be the last couple of years)

when I run select datepart(year, getdate()) it correctly tells me the date is 2018.

Any suggestions about what I'm doing wrong?

Answers 1

Assuming the column is an int, and not a varchar column, you could do this:

datepart(year, c.cko_date + 25567)

25567 is the number of days between 1900-01-01 1970-01-01; essentially this adjusts for the discrepancy between unix and SQL Server datetime.

Max Vernon
Max Vernon
May 23, 2018 11:30 AM

Related Questions

MSDTC Issue SQL Server 2008 R2

Updated August 23, 2016 08:04 AM

Have Subquery return more than 1 Value

Updated January 16, 2018 16:06 PM