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 |
|
danbweb
Starting Member
7 Posts |
 Posted - 2007-09-24 : 08:18:04
|
| Hello - Building a small app for a school nurse......Have a SP that takes 2 parameters (@login, @logout) both in the form of "convert(varchar,@Login,108)" or 08:00 AM, etcWhat I need to do is then check to see:1. What period did they login in?2. What period did they logout in?3. How much time in a period did they actually miss?The ranges look like:Set @Period1Start='08:20 AM'Set @Period1End='09:10 AM'Set @Period2Start='09:15 AM'Set @Period2End='10:00 AM'.........So.....if @login = 08:30 AM and @logout = 08:45 then they missed 15 minutes of period 1If @login = 08:30 AM and @logout = 09:45 then they missed 40 minutes of period 1 AND 30 minutes of period 2Not knowing all of the time functions in SQl, I am looking for some ideas on how to accomplish this.Thanks!Dan B |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-24 : 08:24:06
|
| "Not knowing all of the time functions in SQl, I am looking for some ideas on how to accomplish this."There's not much in SQL that will help you.It would be easier if you could store time as a DATETIME, or (if there is no date involved) as even an INT with 8AM/08:00 stores as 800, and and 7PM/19:00 stores as 1900.Kristen |
 |
|
|
danbweb
Starting Member
7 Posts |
Posted - 2007-09-24 : 12:19:12
|
| But I can do that - I should be able to convert "8:00 am" to DateTime, correct? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-24 : 12:23:05
|
"I should be able to convert "8:00 am" to DateTime, correct?"Yes, but it will be stored as 01-Jan-1900 08:00:00.000You can strip the date off it using CONVERT(), AND (most importantly) you can use all the date/time manipulation functions on it - e.g. DATEDIFF / DATEADDD and so on.I don't know why Microsoft haven't already added a TIME datatype, but it isn't there until SQL2008 I'm afraid  Kristen |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-25 : 01:59:08
|
| "Return that DATETIME value to our client and let it worry about hiding the "1/1/1900""I agree, but only provided that the client can do that.I still think its nuts that there still isn't a TIME datatype For example, Everything arrives with an ado type of datetime, so the application can't automatically determine that its Time, or even just Date ... so you have to explicitly sort out each one, with all the potential for human error that that involves. SO our application assumes that a DateTime with a 00:00:00 time portion is a date only, and a time with a 01-01-1900 date is a time only, but that's not infallible of course.Kristen |
|
|
|
|
|
|
|
|
Time ranges