Searching 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 2000 Forums

Transact-SQL (2000)

Searching Dates

Author

Topic

melcraig
Starting Member

39 Posts

Posted - 2006-05-18 : 16:30:40

Hi everyone,
I need help with searching through dates please,

I have a Leave table

LastName FromDate ToDate

Kelly 06/15/06 06/25/06
Mark 05/01/06 07/01/06
Paul 07/10/06 08/10/06
David 03/03/06 03/08/07

I want to find everone who will be gone between 06/18/06 and 06/20/06.
I can't say FromDate >= 06/18/06 as it will leave out Mark.
Is there a way to search a date range with the date range stored in the DB?
Thanks for helping me,
Mel

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-18 : 17:15:57

Does this work?


[code]
if object_id('tempdb..#tb') > 0 drop table #tb
create table #tb (LastName varchar(10), FromDate datetime, ToDate datetime)
insert #tb
select 'Kelly', '06/15/06', '06/25/06' union
select 'Mark', '05/01/06', '07/01/06' union
select 'Paul', '07/10/06', '08/10/06' union
select 'David', '03/03/06', '03/08/07' union
select 'Irving', '6/17/06', '6/19/06'

select * from #tb
where	fromdate <= '06/20/06'
and	Todate >= '06/18/06'

LastName   FromDate                                               ToDate                                                 
---------- ------------------------------------------------------ ------------------------
David      2006-03-03 00:00:00.000                                2007-03-08 00:00:00.000
Irving     2006-06-17 00:00:00.000                                2006-06-19 00:00:00.000
Kelly      2006-06-15 00:00:00.000                                2006-06-25 00:00:00.000
Mark       2006-05-01 00:00:00.000                                2006-07-01 00:00:00.000

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-18 : 17:29:29

quote:
Originally posted by melcraig
...I want to find everone who will be gone between 06/18/06 and 06/20/06...

Please explain this a little more.

Do you only want people who will be on leave for the entire range of 06/18/2006 to 06/20/2006, or do you want people who will be on leave for any portion of that time range?

In other words, would leave date ranges of 06/16/2006 to 06/18/2006 or 06/19/2006 to 06/30/2006 be included?

CODO ERGO SUM

melcraig
Starting Member

39 Posts

Posted - 2006-05-18 : 18:04:40

TG
That works great. Yea I needed to get everyone that was in any portion of this time period.
Thank you for your help.
Mel

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-19 : 01:57:37

To work with all datesettings use universal format yyyymmdd than local format mm/dd/yyyy or dd/mm/yyyy. Also read this http://www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-19 : 04:08:41

quote:
Originally posted by melcraig

Hi everyone,
I need help with searching through dates please,

I have a Leave table

LastName FromDate ToDate

Kelly 06/15/06 06/25/06
Mark 05/01/06 07/01/06
Paul 07/10/06 08/10/06
David 03/03/06 03/08/07

I want to find everone who will be gone between 06/18/06 and 06/20/06.
I can't say FromDate >= 06/18/06 as it will leave out Mark.
Is there a way to search a date range with the date range stored in the DB?
Thanks for helping me,
Mel

Very easy!

SELECT *
FROM Leave
WHERE CONVERT(DATETIME, FromDate) <= CONVERT(DATETIME, '06/18/06')
AND CONVERT(DATETIME, ToDate) >= CONVERT(DATETIME, '06/20/06')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-19 : 05:42:13

If Fromdate and Todate are Datetime datatypes then no need to convert them

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-19 : 18:13:29

quote:
Originally posted by madhivanan

If Fromdate and Todate are Datetime datatypes then no need to convert them

Madhivanan

Failing to plan is Planning to fail

Of course not, but since the original posting does not state the data type, I was just writing code that would run either way the field is datetime/smalldatetime or char/nchar/varchar/nvarchar/text.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-20 : 07:34:02

Peso, as long as you are being maticulous, notice the difference between your solution and mine (besides the convert). Your code misses Irving (which is the row I added to straddle the date range). Melcraig said "I needed to get everyone that was in any portion of this time period."

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources