Database-safe Date/Time String?

Question

Which format of a date/time string would be considered cross-platform, cross-database, universal safe?

Would this YYYY-MMM-DD HH:MM:SS be considered safe to use in MySQL, SQLite 2 & 3, MsSQL and other common databases?

How about something like 2010-Jul-12 12:00:00pm?

Answer 1

You don't have really have a "safe" DateTime. It all depends on Cultures, UK is DD/MM/YYYY HH:MM:ss US is MM/DD/YYYY HH:MM:ss or whatever not entirely sure if it actually is or not but nontheless it is different.

I would just stick with your "culture", and be consistent throughout you code so furture developers can easilly read you code with figuring out what kind of crazy convention you have for your data and time.

Hope this helps :)

Answer 2

Whenever possible, you should pass and store dates using a real date/time type. Strings will often cause problems if the culture changes (and probably on different database platforms, too).

If your language or database library allows you to use strongly typed parameters in your database queries, that would be the safest way to do this. If you use a date/time type for your columns and pass the values using your language's date/time type, the library will make sure that your dates are handled correctly.

Answer 3

You could pick a defined standard, such as the ISO standard for date and times. It's pretty universal and easy to use and understand, and as a defined standard you can just say "all datetimes must be ISO 8601" and be done with it. No worries about locale or cultural confusion.

Answer 4

You should use the ISO standard - YYYY-MM-DDThh:mm:ss[.mmm]

Or as an alternative YYYYMMDD.

http://msdn.microsoft.com/en-us/library/ms187819.aspx