During the process of migrating a SQL Server database to MySQL I came across the problem of how to handle a SQL Server CAST() function for type SmallDateTime. I was supplied with a SQL dump of the original database and proceeded to write a Windows C# conversion program. The resulting SQL was then imported into the destination MySQL database. There is a lot of good information of converting the various field types, but it was difficult to get the complete picture on how to handle the CAST() function for the SmallDataTime type.

The source database SQL dump  had the following format-
INSERT [dbo].[SITE_Articles] ([ArticleID], [ArticleTitle], [ArticleSubTitle], [ArticleDate],  [ArticleText]) VALUES (604, 6, 1, N’Dummy Article Title’, N’Sub title stuff here’, CAST(0x9B960000 AS SmallDateTime), CONVERT(TEXT, N'<p>In a sweater poorly knit, with an understanding smile…….</p>’);

The problem field was [ArticleDate] with CAST(0x9B960000 AS SmallDateTime).
The format of SQL Server SmallDateTime value is 4 bytes made up of a date part (2 bytes) and a time part (2 bytes). Both of these parts correspond to integer values with reference to the table below.
Date range1900-01-01 through 2079-06-06
January 1, 1900, through June 6, 2079
Time range00:00:00 through 23:59:59
2007-05-09 23:59:59 will round to 2007-05-10 00:00:00

In this case the SmallDateTime value 0x9B960000 has a Date part of 0x9B96 and a Time part of 0x0000.
I was also faced with handling the difference in reference dates between SQL Server and mySQL. With some testing I settled on a offset value of 25567 days.

The result was a function to convert the date field including the  CAST function in the source SQL command to the equivalent mySQL date field command in the destination command.

The code below shows the C# function used in my conversion program.

 Reference
For more information on SQL Server Reference – smalldatetime look here
Hopefully this information will be helpful to others. If you have any comments or questions please leave a comment below.
2,404 views