How do I convert an SQL Server SMALLDATETIME to a Unix timestamp?

Question

How do I convert an SQL Server SMALLDATETIME to Unix Timestamp?

date was stored as CAST(0x96040474 AS SmallDateTime) in MS Access DB. Data was dumped to SQL and I'm looking to convert those times to Unix Timestamps for MySQL.

Thanks AO

Answer 1

select datediff(ss, '1/1/1970', your_date_here)

e.g.

select datediff(ss, '1/1/1970', cast('1/1/1989' as smalldatetime))

Answer 2

From SQL Server 2005 Books Online, "Transact SQL Reference"

The Database Engine stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight.

So it looks like you need to do some splitting of the hex values into two integers and convert from a 1900/01/01 base date to a 1970/01/01 base date, then take the other integer as integral minutes past midnight.

Answer 3

PHP's strtotime() function will take a smalldatetime as an argument.