SQL Server SMALLDATETIME to MySQL?

Databases
1

Hello,
How do I convert the following SQL Server SMALLDATETIME to Unix Timestamp or MySQL date?

CAST(0x9C4902A8 AS SmallDateTime)

I do not have SQL server installed on my computer. I was able to manually convert a SQL server dump to a MySQL dump and import it into MySQL, but the only thing I couldn’t change was the date.

Any help is greatly appreciated.

2

well, i can tell you what that value means, but it will be up to you to write the appropriate code to covert what you have

The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server 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. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.
– http://msdn.microsoft.com/en-us/library/aa258277(v=sql.80).aspx

so your value 0x9C4902A8 consists of two integers, 9C49 and 02A8

hex 9C49 is decimal 40009, and january 1 1900 plus 40009 days is 2009-07-17

hex 02A8 is decimal 680, and midnight plus 680 minutes is 11:20

you can construct the actual datetime value as follows:

select '1900-01-01' 
 + interval cast(0x9C49 as signed) day
 + interval cast(0x02A8 as signed) minute
 as result

but i’m not sure how you should break up the incoming data

3

select datediff(ss, ‘1/1/1970’, your_date_here)

e.g.

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

4

r937 and DmitryS,
With your help I was able to solve the problem. Thanks!!