Professional Web Applications Themes

Working with time - Microsoft SQL / MS SQL Server

Phill, That is surprising. Are the START_TIME and END_TIME columns SQL Datetime or Smalldatetime format? Or are they character strings? If they are character strings, then as they are cast into datatime so that DATEDIFF can operate against them, they acquire the current date as part of the datetime. If this is the case, it would probably be better to use datetime columns. Russell Fields "Phill" <pshawproceedtech.com> wrote in message news:24e901c33f1e$6c0065c0$a601280aphx.gbl... > I am working with a table that has a Start Time and End > Time (in military time format). The problem is when I > calculate elasped time ...

  1. #1

    Default Re: Working with time

    Phill,

    That is surprising.

    Are the START_TIME and END_TIME columns SQL Datetime or Smalldatetime
    format? Or are they character strings?

    If they are character strings, then as they are cast into datatime so that
    DATEDIFF can operate against them, they acquire the current date as part of
    the datetime. If this is the case, it would probably be better to use
    datetime columns.

    Russell Fields
    "Phill" <pshawproceedtech.com> wrote in message
    news:24e901c33f1e$6c0065c0$a601280aphx.gbl...
    > I am working with a table that has a Start Time and End
    > Time (in military time format). The problem is when I
    > calculate elasped time as (DATEDIFF(n, START_TIME,
    > END_TIME)) in a view negative results occur if the person
    > worked through the night and into the next day. Is there
    > a different function I should be using? Thanks.

    Russell Fields Guest

  2. #2

    Default Re: Working with time

    Phill,

    If you are storing only times, with no date part, SQL Server interprets
    them as all on the same day (on January 1, 1900, in fact). It correctly
    calculates the difference, and you will need to add the "next day" context
    to the result, or modify the way things are done to store a date and time,
    so the "next day" values are on January 2.

    While it's impossible to tell from two time values that the only
    possibilities
    are A < B, meaning both times are today, and B < A, meaning B is the next
    day, if you know that's the case, you have to check which you have:

    case when START_TIME < END_TIME then DATEDIFF(n,START_TIME,END_TIME)
    else DATEDIFF(n,START_TIME,END_TIME+1) end

    or

    case when START_TIME < END_TIME then DATEDIFF(n,START_TIME,END_TIME)
    else 1440 + DATEDIFF(n,START_TIME,END_TIME) end

    Steve Kass
    Drew University


    Phill wrote:
    >I am working with a table that has a Start Time and End
    >Time (in military time format). The problem is when I
    >calculate elasped time as (DATEDIFF(n, START_TIME,
    >END_TIME)) in a view negative results occur if the person
    >worked through the night and into the next day. Is there
    >a different function I should be using? Thanks.
    >
    >
    Steve Kass Guest

Similar Threads

  1. 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
  2. working on time
    By Damien.July@Libertysurf.Fr in forum PERL Beginners
    Replies: 10
    Last Post: October 27th, 09:55 AM
  3. Formatting a time field to 24 hour time (Military time) in the Datagrid
    By David Hearn in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: September 10th, 02:50 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