Time data type and negative TimeSpan values?

Question

Time data type and negative TimeSpan values?

kobus.botha

SSC Veteran

Points: 298
More actions
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

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

kobus.botha SSC Veteran Points: 298 More actions · Answer 4

Hi

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

Kind regards.

Kobus

HowardW One Orange Chip Points: 29109 More actions · Answer 5

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)