Professional Web Applications Themes

convert string to date in sql server - Microsoft SQL / MS SQL Server

> select convert(datetime, '2003/08/10 23:59:59.9999', 101)  Yes, because you expect too much accuracy. DATETIME is only accurate to a 3.33 milliseconds, so your .9999 would actually round *UP* to give you midnight. If not for your use of 101.   No, 101 is a 10-character string with no time: mm/dd/yyyy   You should take only a portion of the string, e.g. maybe this will work better: select convert(datetime, convert(datetime, '2003/08/10', 111) + '23:59:59.99') Note that if you do this: select convert(datetime, convert(datetime, '2003/08/10', 111) + '23:59:59.999') It will round up as I described above. And if you do this: select ...

  1. #1

    Default Re: convert string to date in sql server

    > select convert(datetime, '2003/08/10 23:59:59.9999', 101) 

    Yes, because you expect too much accuracy. DATETIME is only accurate to a
    3.33 milliseconds, so your .9999 would actually round *UP* to give you
    midnight. If not for your use of 101.
     

    No, 101 is a 10-character string with no time: mm/dd/yyyy
     

    You should take only a portion of the string, e.g. maybe this will work
    better:

    select convert(datetime, convert(datetime, '2003/08/10', 111) +
    '23:59:59.99')

    Note that if you do this:

    select convert(datetime, convert(datetime, '2003/08/10', 111) +
    '23:59:59.999')

    It will round up as I described above. And if you do this:

    select convert(datetime, convert(datetime, '2003/08/10', 111) +
    '23:59:59.9999')

    You will get this error:

    Server: Msg 241, Level 16, State 1, Line 1
    Syntax error converting datetime from character string.



    And of course if you need to do this from a string that is stored or
    something like that, you can use SUBSTRING to extract the relevant portions:

    declare dtString VARCHAR(24)
    SET dtString = '2003/08/10 23:59:59.9999'
    select convert(datetime, convert(datetime, SUBSTRING(dtString, 1, 10), 111)
    + SUBSTRING(dtString, 12, 11))


    Aaron Guest

  2. #2

    Default Re: convert string to date in sql server

    this will get you the 11th because of rounding - select cast ('2003/08/10
    23:59:59.999' as datetime )

    but this select cast ('2003/08/10 23:59:59.997' as datetime ) gets you
    close...datetime data is rounded

    from BOL " Values are rounded to increments of .000, .003, or .007 seconds,
    as shown in the table."
    --
    HTH,
    David Satz
    Principal Web Engineer
    Hyperion Solutions

    "Mr. J." <ab.com> wrote in message
    news:phx.gbl... 


    David Guest

  3. #3

    Default Re: convert string to date in sql server

    Hi Mr. J.,

    The datetime datatype in SQL Server is only accurate to 3/1000th of a
    second, not to 1/1000th, so the only valid values you can have for
    milliseconds are the ones that end in 0, 3 and 7. All the other ones will be
    rounded. In this case it will be rounded up from 999 to 000 and the time
    will be midnight of the _next_ day. See the following:

    select convert(datetime, '2003/08/10 23:59:59.999', 101)

    If you want the last possible time on August 10 you should have '2003/08/10
    23:59:59.997'

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Mr. J." <ab.com> wrote in message
    news:phx.gbl... 


    Jacco Guest

  4. #4

    Default convert string to date in sql server

    Hello,
    I have a string of date with the format : yyyy/mm/dd hh:mm:ss.nnnn
    I want to convert it to date in the sql query.

    Some thing like this :

    select convert(datetime, '2003/08/10 23:59:59.9999', 101)

    What I get is the date : 10/08/2003, but with hour = 00:00:00 !

    Maybe the type is not 101.

    What type should I use in order to fix the problem ?

    Thanks :)


    Mr. Guest

Similar Threads

  1. How to convert a date string to a Date object
    By kyalong in forum Macromedia Flex General Discussion
    Replies: 6
    Last Post: June 17th, 06:42 AM
  2. convert string to safe string before adding to database
    By forums.macromedia.com in forum Macromedia ColdFusion
    Replies: 4
    Last Post: September 1st, 07:24 PM
  3. Replies: 9
    Last Post: October 24th, 07:31 PM
  4. Replies: 3
    Last Post: October 21st, 07:13 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139