How to code DateTime-Literal in SQL Server

How to code DateTime-Literal in SQL Server

• ciroma

  There are only two unambiguous formats (one for date and another for datetime). Both are ISO formats.

   

  YYYYMMDD - ISO unseparated date format

  YYYY-MM-DDThh:mm:ss.nnn[ Z ] - ISO 8601 timestamp format (note there is no space after and before Z. I have to do it to prevent a stupid icon from showing up and there seems to be no way to prevent formatting.)

   

  The ISO 8601 format is supported only from SQL Server 2000 onwards. Any other format interpretation depends on the language / session settings.

  
  
  

• redbeans

  I am not familiar with the german localized database, but I have some guesses...
  
  You are doing the right thing, dates in SQL are inclosed by single '. Are your clients localized with the same culture as the database Is so you should be able to
  
  strsql = "INSERT INTO TABLE VALUES('" & datetime.tostring() & "')"
  and the tostring method with format it properly.
  
  Also try it with - instead of . in the date.
  
  What I would do is open SQL Query Analyzer and write out the SQL insert query with a date and keep modifying the date format until it accepts it.
  
  If none of this is a help then post the exception message.
  
  

• MkmEV

  Look up date and time formats in books online. There are some ISO date time formates that are the best practice. Generally speaking, the formats are:

  YYYYMMDD or YYYY-MM-DD

  then time:

  HH:MM:SS.SSS

  There are variations, but the most important bits have to do with the year month date arrangement that is not ambiguous in any country

  
  
  
• How to code DateTime-Literal in SQL Server