Professional Web Applications Themes

How to get midnite time always for any given date... - Microsoft SQL / MS SQL Server

answer3: ;-) DECLARE sdt SMALLDATETIME SET sdt = '20030701 23:59' SELECT CAST(FLOOR(CAST(sdt AS FLOAT))AS SMALLDATETIME) Note that my smalldatetime variable is different because: 1) 2003-07-01 is interpreted differently depending on the dateformat set for your user. Only yyyymmdd and yyyy-mm-ddThh:mm:ss.nnn are interpreted independently of the users dateformat. 2) The precision of smalldatetime is minutes, not seconds. That is why the original conversion went wrong, it is rounded up to midnight the next day when you store a time between 23:59:30 and 23:59:59. hth Jacco "Vishal Parkar" <com> wrote in message news:phx.gbl...  as  > > > >[/ref] > >[/ref]...

  1. #1

    Default Re: How to get midnite time always for any given date...

    answer3: ;-)
    DECLARE sdt SMALLDATETIME
    SET sdt = '20030701 23:59'
    SELECT CAST(FLOOR(CAST(sdt AS FLOAT))AS SMALLDATETIME)

    Note that my smalldatetime variable is different because:
    1) 2003-07-01 is interpreted differently depending on the dateformat set for
    your user. Only yyyymmdd and yyyy-mm-ddThh:mm:ss.nnn are interpreted
    independently of the users dateformat.
    2) The precision of smalldatetime is minutes, not seconds. That is why the
    original conversion went wrong, it is rounded up to midnight the next day
    when you store a time between 23:59:30 and 23:59:59.

    hth Jacco


    "Vishal Parkar" <com> wrote in message
    news:phx.gbl... 
    as 
    > >
    > >[/ref]
    >
    >[/ref]


    Jacco Guest

  2. #2

    Default Re: How to get midnite time always for any given date...

    Here is a problem!!!


    DECLARE sdt SMALLDATETIME
    SET sdt = '2003-07-01 23:59:57'


    Because the precision of SMALLDATETIME is to the minute, this automatically
    gets rounded up to 2003-07-02 12:00:00 AM before you do anything to it at
    all!


    DECLARE sdt DATETIME
    SET sdt = '2003-07-01 23:59:57'
    SET sdt = CONVERT(SMALLDATETIME, CONVERT(CHAR(8), sdt, 112))
    SELECT sdt







    "Ricky" <state.mn.us> wrote in message
    news:007401c35158$a9ed9f00$gbl... 


    Aaron Guest

  3. #3

    Default Re: How to get midnite time always for any given date...

    Ricky,

    The smalldatetime data type is only precise to the nearest minute.
    As a result, when you execute

    declare sdt smalldatetime
    set sdt = ' 2003-07-01 23:59:57'

    the value of sdt is July 2, 2003 at midnight. You must either use
    datetime (accurate to 1/300 of a second) or wait to put the
    string into smalldatetime until after rounding down.

    Here is an idea of how you might round to various precisions:

    create function roundDateDownTo(
    d datetime,
    u varchar(20)
    ) returns datetime as begin

    declare keepchars int
    set keepchars = case u
    when 'year' then 4
    when 'month' then 7
    when 'day' then 10
    when 'hour' then 13
    when 'minute' then 16
    when 'second' then 19
    end

    return stuff('1900-01-01T00:00:00.000',
    1,
    keepchars,
    left(convert(char(23),d,126),keepchars)
    )
    end
    go

    select u as Unit, dbo.roundDateDownTo(getdate(),u) as roundedDate
    from (
    select 'year' union all select 'month' union all select 'day'
    union all select 'hour' union all select 'minute' union all
    select 'second' union all select 'unknown'
    ) T(u)
    go

    drop function roundDateDownTo
    go



    Steve Kass
    Drew University


    Ricky wrote:
     

    Steve Guest

Similar Threads

  1. date without time
    By Kun in forum MySQL
    Replies: 3
    Last Post: April 13th, 07:43 PM
  2. CFMX7.0.1 Administrator date time issue showing 13hrsbehind server time
    By bayfield in forum Coldfusion Server Administration
    Replies: 0
    Last Post: December 8th, 04:34 AM
  3. Replies: 3
    Last Post: October 21st, 07:13 PM
  4. Time/Date format and changing time to GMT
    By Shaiboy_UK in forum ASP
    Replies: 1
    Last Post: October 19th, 01:04 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