Post reply

Time data type and negative TimeSpan values?

  • September 9, 2011 at 2:14 am

    #257140

    Hi

    I am busy working on a windows application to log working times of staff. The application is being developed using C# with SQL Server 2008 as the backend.

    I have a [NetTime] column (Time data type) in my table in the database. I get the TimeSpan value by subtracting the official working hours and the actual working hours. This TimeSpan value can be positive or negative. I would like to store this value in the [NetTime] column, but the Time data type does not accept negative values.

    What is the usual way of dealing with this problem? I will need to do calculations with the values in this column, for example to calculate the net or total time a staff member worked overtime or not.

    Thank you.

    Kobus

  • September 9, 2011 at 3:36 am

    #1379089

    The difference between two times isn't a time, it's a quantity. You could use an integer datatype to store the number of minutes difference between the two times - easily resolved out to hours and minutes difference.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • September 9, 2011 at 4:37 am

    #1379108

    Both of these works, just figure out for yourself if the value fits better as an integer or a float/decimal.

    declare @Datetime1 as datetime = getdate(),

    @Datetime2 as datetime = dateadd(mi,-20,getdate())

    select datediff(s,@Datetime1,@Datetime2 ),

    CAST(@Datetime2 - @Datetime1 as float)

    Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

  • September 9, 2011 at 4:53 am

    #1379113

    The Time data type is not a timespan. It's a point in time. So 04:30 is half past 4 in the morning, not a duration of 4 hours 30 min. If you want to store time durations, use an integer and decide at what granularity you want to store the time duration at (seconds, minutes, hours, etc)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 9, 2011 at 4:57 am

    #1379116

    Hi

    Thank you for the replies. I am now busy changing my application to implement the changes.

    Kind regards.

    Kobus

  • September 9, 2011 at 5:07 am

    #1379121

    There would be a good argument for implementing this as a computed column (possibly persisted or indexed depending on it's use) if it will always be calculable based on other data in the same row?

    e.g:

    Alter Table MyTable

    ADD MyColumn as DATEDIFF(Minute, Column1,Column2)

Viewing 6 posts - 1 through 5 (of 5 total)

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