Extract between dates - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Extract between dates

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2011-11-21 : 06:27:36

Hello,
having this variable, only Date (no hours)

DECLARE @TestDate DATE = '2011-11-20'

I need to extract records from a table that has StartDate and EndDate of DateTime type.

If I run:

SELECT * FROM dbo.values
WHERE @TestDate BETWEEN StartDate AND EndDate

I obtain

ID StartDate EndDate Valore
2 2011-11-19 12:30:00.000 2011-11-20 18:30:00.000 7.000

but I need to extract another record, that has the same start date (but in hour ahead)

ID StartDate EndDate Valore
9 2011-11-22 00:30:00.000 2011-11-22 18:30:00.000 10.000

I can I obtain this result?

Luigi

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-11-21 : 06:33:36

SELECT dateadd(dd,datediff(dd,0,[DateTime Column Name]),0) as DateOnly from TblTableName

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 06:39:29

quote:
Originally posted by Ciupaz

Hello,
having this variable, only Date (no hours)

DECLARE @TestDate DATE = '2011-11-20'

I need to extract records from a table that has StartDate and EndDate of DateTime type.

If I run:

SELECT * FROM dbo.values
WHERE @TestDate BETWEEN StartDate AND EndDate

I obtain

ID StartDate EndDate Valore
2 2011-11-19 12:30:00.000 2011-11-20 18:30:00.000 7.000

but I need to extract another record, that has the same start date (but in hour ahead)

ID StartDate EndDate Valore
9 2011-11-22 00:30:00.000 2011-11-22 18:30:00.000 10.000

I can I obtain this result?

Luigi

how do you think you will obtain the red record above. its having date on 2011-11-22 whoch is outside your range (2011-11-20)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ciupaz
Posting Yak Master

232 Posts

Posted - 2011-11-21 : 07:56:38

Sorry, the correct value is:

DECLARE @TestDate DATE = '2011-11-22'

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 08:51:15

quote:
Originally posted by Ciupaz

Sorry, the correct value is:

DECLARE @TestDate DATE = '2011-11-22'

Luigi

Sorry still confusion persists
how did you get the first record shown? its not in range for 2011-11-22

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ciupaz
Posting Yak Master

232 Posts

Posted - 2011-11-21 : 09:25:01

It's better rewrite all.

I have these values in my table:

ID StartDate EndDate Value
1 2011-11-20 12:30:00.000 2011-11-20 18:30:00.000 9.000
2 2011-11-19 12:30:00.000 2011-11-20 18:30:00.000 7.000
3 2011-11-01 12:30:00.000 2011-11-10 18:30:00.000 5.000
4 2011-10-20 12:30:00.000 2011-10-21 18:30:00.000 5.000
5 2011-09-20 12:30:00.000 2011-11-03 00:30:00.000 43.000
6 2010-12-31 19:30:00.000 2011-01-01 18:30:00.000 20.000
7 2011-10-10 12:30:00.000 2011-10-31 18:30:00.000 9.000
8 2011-10-22 00:30:00.000 2011-10-25 18:30:00.000 10.000
9 2011-11-22 00:30:00.000 2011-11-22 18:30:00.000 10.000

If I run this query:

DECLARE @TestDate DATE = '2011-11-20'

SELECT * FROM dbo.values
WHERE @TestDate BETWEEN StartDate AND EndDate

I get:

ID StartDate EndDate Value
2 2011-11-19 12:30:00.000 2011-11-20 18:30:00.000 7.000

(1 row(s) affected)

but for my specs, I need to obtain also the record 1, because it includes the day 20.

Luigi

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-11-21 : 10:24:50


create table #values(ID int, StartDate datetime, EndDate datetime,Value decimal (10,3))

insert into #values (ID,StartDate,EndDate,Value )
select 
1 ,'2011-11-20 12:30:00.000', '2011-11-20 18:30:00.000', 9.000 union all select
2 ,'2011-11-19 12:30:00.000', '2011-11-20 18:30:00.000', 7.000 union all select
3 ,'2011-11-01 12:30:00.000', '2011-11-10 18:30:00.000', 5.000 union all select
4 ,'2011-10-20 12:30:00.000', '2011-10-21 18:30:00.000', 5.000 union all select
5 ,'2011-09-20 12:30:00.000', '2011-11-03 00:30:00.000', 43.000 union all select
6 ,'2010-12-31 19:30:00.000', '2011-01-01 18:30:00.000', 20.000 union all select
7 ,'2011-10-10 12:30:00.000', '2011-10-31 18:30:00.000', 9.000 union all select
8 ,'2011-10-22 00:30:00.000', '2011-10-25 18:30:00.000', 10.000 union all select
9 ,'2011-11-22 00:30:00.000', '2011-11-22 18:30:00.000', 10.000 

DECLARE @TestDate DATE = '2011-11-20'

SELECT * FROM #values
WHERE @TestDate BETWEEN dateadd(dd,datediff(dd,0,StartDate),0) AND dateadd(dd,datediff(dd,0,EndDate),0)

drop table #values

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources