Sunday, June 21, 2009

Scripting SQL datetime fields and avoiding localization problems

If you need to export you data to a .sql file for various reasons, you have different options when “serializing” datetime fields. Often they are scripted as: ‘2009-05-11 11:00’ or ‘May 5 2009 12:00AM’. This will break if the script is run with a tool/server that uses a different locale (like a European/Asian), and cause either run-time errors or even worse, incorrect dates in the database.

The Database Publishing Wizard / Publish to Provider… uses this format:

CAST(0x00008C3300000000 AS DateTime)

which is probably correct and portable, but not very notepad friendly.

I use the ODBC Escape sequence formatting, which looks like this, and works across any locale (and is more friendly on the eye!):

{ts ‘2009-05-11 23:00:00’}

Here’s the .NET code to create this format:

_sbScript.Append("{ts '");
_sbScript.Append(date.ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture));
_sbScript.Append("'}");

No comments: