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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-25 : 09:20:28
|
| Alex writes "Hi,I've got a database with a collection of records relating to broadcast programs. Each record has a start time, a weekday number, and duration. What query should I use to pick out a record based on current time? I've tried using datediff and dateadd, but come up against problems with a certain record that has a start time of 20:00hrs and a duration of 240 minutes - it pushes over to midnight, and it seems to end up being a date in 1890 instead of an actual time.I had it working in Access by doing:SELECT [progdefault].[day], [progdefault].[starttime], [progdefault].[duration], [progdefault].[progid], DateAdd("n",[duration],[starttime]) AS expr1FROM progdefaultWHERE ((([progdefault].[day])=3) And (([progdefault].[starttime])<Time()) And ((DateAdd("n",[duration],[starttime]))>Time()));.. but having migrated to SQL Server last week It's got me stumped!Thanks, hope you can helpAlex" |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-03-25 : 09:24:28
|
| what manipulations are you trying with dateadd() and datediff()? give us a code snippet and a few example rows ...setBasedIsTheTruepath<O> |
 |
|
|
Teroman
Posting Yak Master
115 Posts |
Posted - 2002-03-25 : 11:50:11
|
| Looks like you are trying to select every thing that is on now, right?Afraid I dont know what the day thing is though, is that supposed to be today as a day of the week?i assume that the times are in minutes from midnightto get everything on now:select day, starttime, duration, progid, duration+starttimefrom progdefaultwhere datepart(dw,getdate()) = dayand getdate between dateadd(n, starttime, convert(varchar,getdate(), 106)) and dateadd(n, starttime+duration, convert(varchar,getdate(), 106))not sure im selecting the right thinks, but do think the where clause is okcol |
|
|
|
alexk
Starting Member
2 Posts |
Posted - 2002-03-25 : 11:53:53
|
| Hi,Here are some sample records for a day. Start time is an actual time, duration is in minutes. id day starttime duration progid1 1 00:00:00 60 12 1 01:00:00 540 23 1 10:00:00 240 134 1 14:00:00 60 15 1 15:00:00 240 46 1 19:00:00 60 17 1 20:00:00 240 2It's 4.52pm at the moment, so we'd need to retrieve record id #5, as the start time is less than the current time, and the end time (start time plus duration) is greater than the current time. Simple < and > operators don't seem to do the trick, hence the attempts with datediff. Dateadd was used to get the end time.Alex |
|
|
|
Teroman
Posting Yak Master
115 Posts |
Posted - 2002-03-25 : 11:57:05
|
| dont see how you can do this as SQL server can NOT actually store times individually.what do you see if you do select * from table?would suggest putting the start times as a number of minutes from midnight, and using the query i posted above, I think its ok, give it a whirlcoledit, added the word NOT, minor mistake ;)by way of a PS, you should try selectdatepart(dw, getdate())too, just to make sure you and the server agree on what day of the week it is now.Edited by - teroman on 03/25/2002 11:58:50 |
|
|
|
alexk
Starting Member
2 Posts |
Posted - 2002-03-26 : 15:14:17
|
| Actually when I do SELECT * I get proper times, like 10:00:00 - the field type is datetime, length 8. However... this table was imported from Access so anything is possible :-)I've added a new column called startmintime, which has the start time expressed as minutes since midnight. Your query seems to work nicely, only slight problem I've got now is the SQL server clock is four minutes fast! Not a major problem to get fixed, so for the moment looks like we've got a working solution.CheersAlex |
|
|
|
|
|
|
|
|
Selecting a record based on current time