none
How to code DateTime-Literal in SQL Server

    Question

  • Hi,

    I'm a newbee to SQL Server. I have a very simple question to you experts: How should I code a literal of the "datetime"-Datatype?  For Example in the VALUES-clause of an SQL-statement. I have tested several "formats" ('20.04.2006 11:15:00' with an 4-digit year enclosed in single apostrophes) but all i earned is an exception!

    Any help very appreciated!

    Thanks in advance and best regards

    Reiner

    PS.: I'm using a german-localized database (thus the date-format dd.MM.yyyy).

     

     

    Thursday, April 20, 2006 1:53 PM

Answers

  • 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.
    Tuesday, April 25, 2006 1:18 AM

All replies

  • 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.
    Thursday, April 20, 2006 2:03 PM
  • 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

    Friday, April 21, 2006 2:08 AM
    Moderator
  • 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.
    Tuesday, April 25, 2006 1:18 AM
  • The question was asked as a literal, which would mean no implicit type conversion taking place from char to datetime.  The proposed answer appears incorrect.  I believe they're suggesting that you define a literal char and implicitly cast it into a datetime data type.

    I am still looking to find if there is a notation for defining datetime literals inside of T-SQL.  I've used SQL for many years and I have yet to see the syntax.

    Here is an example in VB.NET using the syntax

    Dim d As Date
    d = # 8/23/1970 3:45:39AM #

    http://msdn.microsoft.com/en-us/library/aa711653%28v=vs.71%29.aspx

    Is there an answer to the question about a way to define a literal (not a char cast to datetime)?


    • Edited by Jeff Fischer Wednesday, January 16, 2013 2:33 AM Correction
    Wednesday, January 16, 2013 2:16 AM
  • This looks like it could be a link to the real answer - http://dzone.com/snippets/date-literals-sql.

    Wednesday, January 16, 2013 2:22 AM