Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rohcky
Starting Member
38 Posts |
 Posted - 2005-05-09 : 12:05:48
|
| I have a table that holds information on a number a facilities and the dates and times that they have been used. For instance:name date timestart timeend---------------------------------fac1 4/1/05 3:00pm 8:00pmfac1 4/2/05 4:00pm 6:00pmfac2 4/1/05 4:00pm 6:00pmHow do I calculate the number of hours that facilities were used, so the output would be: fac1 used for 7 hrs (or 420 mins) and fac2 used for 2 hrs (or 120 mins)?(this is in a DTS by the way, not sure if that makes a difference) |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-05-09 : 12:18:49
|
| Let me Guess these are all char fields.Is this a new devopment or is the table pre-existing?If this is new I would fix the table structure first.Starttime and endtime should be datetime fields and you will not need a "date" field.Then Just use datediff between the two for your time.JimUsers <> Logic |
 |
|
|
rohcky
Starting Member
38 Posts |
Posted - 2005-05-09 : 12:44:00
|
| table is pre-existing and yes the fields are char fields. I could alter-add two datetime fields and copy the text times into the new fields, then use the datediff function. Thanks for the help JimL, I couldn't find the function datediff and didn't find it on google. |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-05-09 : 12:55:22
|
| FROM BOLThe DATEDIFF function calculates the amount of time in dateparts between the second and first of two dates you specify. In other words, it finds an interval between two dates. The result is a signed integer value equal to date2 - date1 in date parts.This query uses the date November 30, 1995, and finds the number of days that elapsed between pubdate and that date:USE pubsSELECT DATEDIFF(day, pubdate, 'Nov 30 1995')FROM titlesFor the rows in titles having a pubdate of October 21, 1995, the result produced by the last query is 40. (There are 40 days between October 21 and November 30.) To calculate an interval in months, use this query:USE pubsSELECT interval = DATEDIFF(month, pubdate, 'Nov 30 1995')FROM titlesThe query produces a value of 1 for the rows with a pubdate in October and a value of 5 for the rows with a pubdate in June.When the first date in the DATEDIFF function is later than the second date specified, the resulting value is negative. Because two of the rows in titles have values for pubdate assigned using the GETDATE function as a default, these values are set to the date that your pubs database was created and return negative values in the two preceding queries.If one or both of the date arguments is a smalldatetime value, they are converted to datetime values internally for the calculation. Seconds and milliseconds in smalldatetime values are automatically set to 0 for the purpose of calculation.JimUsers <> Logic |
|
|
|
|
|
|
|
|
Calculating Total hours