Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Error with SQL Standard Date

    Hi

    Playing around on server at new giggerooo.

    Code:
    
    SELECT CAST('2007-01-28'ASDATETIME)
    
    throws:

    Msg 242, Level 16, State 3, Line 1
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
    This:
    Code:
    
    SELECT CAST('20070128'ASDATETIME)
    
    Works fine.

    ASFAIK (or recall) former is SQL Standard, latter is ISO.

    Should I just start using the ever-so-slightly-less-readable YYYYMMDD format or is there a specific setting I should be looking at and perhaps change?

    SS 2K
    Language: English (United States)
    Collation: Latin1_General_CI_AI

    Ta
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    set dateformat ymd
    SELECT CAST('2007-01-28' AS DATETIME)
    tadaaaaaa!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks Rudy - that will sort it out just fine.

    To save my poor fingers typing that out everytime I use a date - do you know what the server\ database level equievalent is?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry pootle, i don't know

    i am not a DBA, so i'm more or less oblivious to server settings
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by pootle flump
    do you know what the server\ database level equievalent is?
    What do you mean with this question?

    Note: I find it good practice to always tell SQL Server how the string you want to transform into a date is formatted instead of relying on (changable) defaults. This means using the CONVERT(type, string, format) syntax instead of CAST:
    Code:
    CONVERT(DATETIME, '20070128', 112)
    Ofcourse when doing some ad hoc querying it's no problem when you rely on the default but in a stp or jobscript, etc. it's better to be sure.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Lexiflex
    This means using the CONVERT(type, string, format) syntax instead of CAST
    Lexiflex.rep++
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by r937
    Lexiflex.rep++
    I don't know what this means but my ego has decided to take it as a compliment

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    In order to check the existing value of dateformat, run
    dbcc useroptions.

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by Lexiflex
    I don't know what this means but my ego has decided to take it as a compliment
    it means, add one to your good reply count, but return the value of the count before the add.

    more efficient is to use the prefix version:

    Code:
    ++Lexiflex.rep
    because the count doesn't have to be stored in a temp.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    close

    rep := reputation

    a feature of vBulletin forums which is disabled in dbforums.com

    i was using the javascript syntax

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    heh, i was just kidding around. I had no idea what it really meant.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •