3

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

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Could you please refine your question? A unix file timestamp? What particular format do you want? BTW, I assume this is MS SQL you're asking about? – Ken Gentle Nov 20 '08 at 23:59
  • It was MS SQL -- but it was dumped out. And I'm importing into MySQL. –  Nov 21 '08 at 01:28
  • I have alot of records that have a date that looks like this CAST(0x96040474 AS SmallDateTime) and no way to convert it to a unix timestamp. –  Nov 21 '08 at 01:29
  • Why is Access relevant? I see no reason for the tag. – David-W-Fenton Nov 21 '08 at 04:20

3 Answers3

1

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

e.g.

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

Preet Sangha
  • 64,563
  • 18
  • 145
  • 216
0

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

0

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.

Ken Gentle
  • 13,277
  • 2
  • 41
  • 49
  • Ken -- can you convert a couple based on your formula above? I can cross reference the dates to see if they come out right. 0x96040474 0x9A9C03C0 Thanks AO –  Nov 22 '08 at 03:59
  • CAST(0x9A9C03C0 AS SmallDateTime) and CAST(0x96040474 AS SmallDateTime) –  Nov 22 '08 at 04:01