Query info between time range & date range

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query info between time range & date range

Author

Topic

jpatton
Starting Member

3 Posts

Posted - 2006-08-16 : 12:55:50

I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-16 : 13:22:59

Post some sample data & expected results

Srinika

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-16 : 13:29:38

[code]
declare @timeFrom int, @timeTo int, @dateFrom datetime, @dateTo datetime
select @timeFrom = 14, @timeTo = 18, @dateFrom = getdate()-5, @dateTo = getdate()

select * from table1
where datecol1 between @dateFrom and @dateTo
and datecol1 between
dateadd(hh, @timeFrom, DATEADD(d, DATEDIFF(d, 0, datecol1), 0))
and
dateadd(hh, @timeTo, DATEADD(d, DATEDIFF(d, 0, datecol1), 0))
[/code]

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

jpatton
Starting Member

3 Posts

Posted - 2006-08-16 : 14:37:23

These are the errors I get when I try to run that query:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '2:00'.
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@'.
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@'.

I used:
declare @'2:00' int, @'6:00' int, @'8/1/06'datetime, @'8/14/06'datetime
select @'2:00' = 14, @'6:00' = 18, @'8/1/06' = getdate()-5, @'8/14/06' = getdate()

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-16 : 14:42:01

emm are you at all familiar with sql variables??

declare @timeFrom int, @timeTo int, @dateFrom datetime, @dateTo datetime
select @timeFrom = 14, @timeTo = 18, @dateFrom = '20060801' , @dateTo = '20060814'

14 and 18 are 24 hour formats for 2pm and 6pm.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

jpatton
Starting Member

3 Posts

Posted - 2006-08-16 : 14:59:22

Nope, not very familiar w/ SQL at all (hence the forum name "New to SQL Server"). Kinda got thrown into it... a sorta learn as you do thing.... Thanks for aall your help!! I will try this and see if it will pull the results I need... Thanks!!

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-16 : 15:09:56

http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Also Google for "T-SQL" tutorials

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources