Post reply

Date Format

  • February 28, 2005 at 10:28 am

    #87732

    Can data in this format DDMMYYYY be stored in a datetime column?   I have created a temp table with the column define is a datatime, but when I inserted the value it failed because it was out of range?  Do I have to change the data type to nchar/varchar? 

  • February 28, 2005 at 12:02 pm

    #544478

    Will your data always be in that format?  If so, you have a number of options.  Because I am American, I would prefer the following:  13071961 would be the value. 

    SELECT SUBSTRING( '13071961', 3, 2) + '/' + SUBSTRING( '13071961', 1, 2) + '/' + SUBSTRING( '13071961', 5, 4)

    I wasn't born stupid - I had to study.

  • February 28, 2005 at 12:06 pm

    #544479

    MY GOOF!  I had the wrong numbers in the SUBSTRING. 

    This will work: 

    SELECT CONVERT( datetime, SUBSTRING( '13071961', 3, 2) + '/' + SUBSTRING( '13071961', 1, 2) + '/' + SUBSTRING( '13071961', 5, 4))

     

    FORGET THIS KNUCKLEHEADED APPROACH....

    I played with this a bit more and found an odd outcome that I do not understand. 

    SELECT CONVERT( datetime, SUBSTRING( '13071961', 2, 2) + '/' + SUBSTRING( '13071961', 1, 2) + '/' + SUBSTRING( '13071961', 5, 4))

    Results in the following error:  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. 

    (If that is out of range, I am too old to use SQL Server...    )

     

    p.s. I tried converting it to varchar as well and smalldatetime does not work either...  

    I wasn't born stupid - I had to study.

  • March 1, 2005 at 1:10 am

    #544542

    Hi Farrell,

    the error is caused by SUBSTRING( '13071961', 2, 2) , which should be SUBSTRING( '13071961', 3, 2) . Just a typo, I guess, because you have it right in the previous example.

  • March 1, 2005 at 2:37 am

    #544549

    See if this provides additional help:

    http://www.karaszi.com/sqlserver/info_datetime.asp

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 1, 2005 at 10:24 am

    #544652

    FYI. This won't help much for dates in "mmddyyyy" format, but for dates in "yyyymmdd" format its faster and easier to use the CONVERT function. This has an optional third "style" argument specifically for handling different formats. See SQL Books Online.

    convert( datetime, '20050228', 112)

    This would also allow you to simply rearrange the digits with SUBSTRING and + but not bother with the two '/' literals.

    If your code might have to work with different language settings, then it should probably also use CONVERT with a style so you don't run into problems with USA 02/28/2005 not being recognized as 28.02.2005 under British/French setting.

    David Lathrop
    DBA
    WA Dept of Health

  • March 1, 2005 at 1:33 pm

    #544691

    See

    SET DATEFORMAT dmy

    Kindest Regards,

    Vasc

  • March 2, 2005 at 2:01 pm

    #545025

    This may just be academic, but..

    Remember that when you talk about the "format" of a date/time value, you're strictly talking about presentation.  No matter how the date/time is represented, the actual datetime datatype is stored as two numbers that specify the number of days since Jan 1, 1900 and the number of milliseconds since midnight.

    As has already been posted, you can explicitly define which format to use in a CONVERT function, or use SET DATEFORMAT to set the default.

  • March 3, 2005 at 2:24 am

    #545131

    the actual datetime datatype is stored as two numbers that specify the number of days since Jan 1, 1900 and the number of milliseconds since midnight

    That's incorrect information from BOL.

    Now, the second 4 bytes of a DATETIME do store the time. Try and enter a value of 300 into this part and you'll get 1 second as a result.

    Consider this:

    DECLARE @300 BINARY(8)

    SET @300 = 0x00000000 + CAST(300 AS BINARY(4))

    SELECT @300, CAST(@300 AS DATETIME)

                                                                         

    ------------------ ------------------------------------------------------

    0x000000000000012C 1900-01-01 00:00:01.000

    (1 row(s) affected)

    So, when the integer value of 300 results in 1 second, one can come to the reverse conclusion that *not* 300 milliseconds are stored, but rather 300/300 of a second, that is 1 second.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 3, 2005 at 4:52 am

    #545157

    Maybe thats wher the accuracy comes into play,

    From BOL,

    Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.

     

    --------------------
    Colt 45 - the original point and click interface

  • March 3, 2005 at 5:17 am

    #545163

    It depends. Stored in a DATETIME are the clock ticks since midnight. Every second consist of 300 ticks. They are more precise than @@TIMETICKS.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply