Results 1 to 8 of 8

Thread: date/time bug?

  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: date/time bug?

    Hi

    I've just noticed something unusual happening when entering into a field with a date/time data type.

    When entering 31/06/2005 it displays a message saying:-

    The value you entered is not consistent with the data type or length of the column.
    But if I enter 31/06/05, it changes the value to 05/06/1931.

    Is this a known problem with SQL Server?

    I did a search on google, but nothing came up.

    Thanks

  2. #2
    Join Date
    Jun 2003
    Posts
    269
    Quote Originally Posted by KevCB226
    Hi

    I've just noticed something unusual happening when entering into a field with a date/time data type.

    When entering 31/06/2005 it displays a message saying:-



    But if I enter 31/06/05, it changes the value to 05/06/1931.

    Is this a known problem with SQL Server?

    I did a search on google, but nothing came up.

    Thanks
    hmmmmmmmmm.......June only have 30 days.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    When you enter 31/06/2005 SQL Server is able to figure out that the 2005 represents the year and the 31 represents the day, and generates an error because of course "30 days hath September, April, June, and November...".

    When you enter 31/06/05 SQL Server doesn't know which element represents the year, and incorrectly guesses the first one.

    To be sure, submit you date values as datetime datatypes, or in the standard format "YYYY-MM-DD", which all implementation of SQL Server will interpret correctly.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Sep 2005
    Posts
    240
    I now realise it's because I'm trying to enter the date in as UK format.

    Is there anyway to get SQL Server to accept dates in UK format?

    I have tried by setting the db login's default language to 'British English', but that hasn't worked

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    June has 31 days in the UK? Well I'll be darned...

    You can submit your date value as a string in UK format and use the CONVERT function to change it to a datetime datatype. CONVERT will allow you to specify the format of the input string.
    Hint: to find the CONVERT function in Books Online, look up CAST AND CONVERT.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2005
    Posts
    115
    good luck Kev with SQL dates ! :-) i am on it since 2 weeks .. and a lot of help here ... but at the end it doesnt work for me without

    (CONVERT(DATETIME, '30/06/2005', 120)

    or

    (CONVERT(DATETIME, '2005-06-30', 120)

    this can help : http://www.sqljunkies.com/Article/66...C7FD826E5.scuk
    Last edited by quentin; 02-21-06 at 12:26.

  7. #7
    Join Date
    Sep 2005
    Posts
    240
    Quote Originally Posted by blindman
    June has 31 days in the UK
    lol, your right to question that, no it doesn't have 31 days.
    I've been reading the dates off a table to enter them into the database, and someones gone and done a typo

    Now I look like a right noob.

    At least If it happens again, I'll know why.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by quentin
    good luck Kev with SQL dates ! :-) i am on it since 2 weeks .. and a lot of help here ... but at the end it doesnt work for me without

    (CONVERT(DATETIME, '30/06/2005', 120)

    or

    (CONVERT(DATETIME, '2005-06-30', 120)
    '30/06/2005' and '2005-06-30' are not the same format, so I don't know why you would expect casting them both from format 120 would work. Quentin, you really need to review the syntax of the functions you are using. The information is easily available in Books Online. Or at Microsoft's site here:
    http://msdn.microsoft.com/library/de...ortal_7ap1.asp
    The format of your first date string is 103 (British/French), not 120.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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