6

I have this question since the dawn of my programming career. Is there really a culture neutral format for datetime in sql server so that when ever I'm sending query string from a client side to the database server running sql server 2008 side having different system datetime format, and if the query contains a date converted to a string, what format would not give me an error.

Soham Dasgupta
  • 5,061
  • 24
  • 79
  • 125

2 Answers2

12

I recommend reading Tibor Karaszi's The ultimate guide to the datetime datatypes.

If you input your date/time in unseparated or ISO 8601 format, then you should be fine with any configuration.

Unseparated = 'yyyymmdd hh:mm:ss'
ISO 8601    = 'yyyy-mm-ddThh:mm:ss'

Update (from comments):

If you need to enter a date only (without the time part), then you have to use the 'YYYYMMDD' format, as 'YYYY-MM-DD' will not work. See the linked article for an example.

Spi
  • 686
  • 3
  • 18
MicSim
  • 26,265
  • 16
  • 90
  • 133
  • 3
    For SQL Server and `DATETIME` types, you need to use either `YYYYMMDD` (**no dashes!** if you need DATE only - no time), or the format you mentioned (`yyyy-mm-ddThh:mm:ss`) - both work. – marc_s Jan 11 '12 at 09:24
  • Yes, i mentioned both. I usually prefer the "no dashes" version. It's more concise. – MicSim Jan 11 '12 at 09:25
  • Not really - you didn't mention the format if you need date only (without time). Contrary to what one could be lead to believe, `YYYY-MM-DD` would **NOT** work (while `YYYYMMDD`) does.... – marc_s Jan 11 '12 at 09:26
  • At the risk of sounding stupid, how does one specify timezone? In that case, wouldn't UTC date format be appropriate? – shahkalpesh Jan 11 '12 at 09:32
  • 2
    The single timezone aware type in SQL Server is datetimeoffset (since 2008). See http://msdn.microsoft.com/en-us/library/bb630289.aspx for details. – MicSim Jan 11 '12 at 09:45
  • @MicSim: If the date comes from the client (sending it to a web server), what timezone, the date would be in? i.e. lets say the client sends 20121112T16:44:00, shouldn't it specify timezone as well? – shahkalpesh Jan 11 '12 at 11:14
  • You would never just get the data that the client sends you and put it in database. You, as the recipient should have control (or at least knowledge) of what date information the client is supposed to enter/send. Then you have to parse and transform this date according to your storing schema in the database. Never use database representation for view representation (and vice versa)! – MicSim Jan 11 '12 at 11:31
  • When targeting a `DATE` type (as distinct from say `DATETIME`) I would still prefer the ISO 8601 format with midnight for the time element e.g. `CAST('2012-01-11T00:00:00' AS DATE)`. More verbose, yes, but easier to read and is Standard ;) – onedaywhen Jan 11 '12 at 11:48
  • The EXCELLENT reference link is broken. Its new address is https://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes. – Spi Nov 15 '19 at 07:09
0

You could consider storing your date/times in UNIX time format.

Gaurav Gupta
  • 5,380
  • 2
  • 29
  • 36