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 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 range | 1900-01-01 through 2079-06-06 January 1, 1900, through June 6, 2079 |
| Time range | 00: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.
static String FixDateField(String strInput)
{
String strDateNumber = "";
UInt32 nDateNumber = 0;
String strCommand = "";
UInt32 nMySQLDate = 0;
if(strInput == "")
{
return "";
}
strDateNumber = strInput.Substring(5, 6);
nDateNumber = Convert.ToUInt32(strDateNumber, 16);
nMySQLDate = nDateNumber - 25567;
strCommand = "DATE_ADD('1970-01-01', INTERVAL " + nMySQLDate + " DAY)";
return strCommand;
}