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 2005 Forums
 Transact-SQL (2005)
 Query specific date between 2 date fields?

Author  Topic 

lagvoid
Starting Member

8 Posts
Posted - 2007-09-10 : 22:33:10
This is probably really simple, but it's giving me a tough time.

I have to date fields. StartDate and EndDate.

How could I find records where the date is between those fields?

The records I'm looking for could be = to the startdate, = to the enddate, or between both date fields.

I appreciate any help!

Zoroaster
Aged Yak Warrior

702 Posts
Posted - 2007-09-10 : 22:51:22
quote:
Originally posted by lagvoid

This is probably really simple, but it's giving me a tough time.

I have to date fields. StartDate and EndDate.

How could I find records where the date is between those fields?

The records I'm looking for could be = to the startdate, = to the enddate, or between both date fields.

I appreciate any help!



What columns do you have in your table, is there a date column?


Future guru in the making.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-09-10 : 22:57:35
[code]where @yourdate >= startdate
and @yourdate <= enddate[/code]

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

lagvoid
Starting Member

8 Posts
Posted - 2007-09-10 : 23:02:49
I tried a query similar to that but it didn't produce the correct results. Maybe my syntax is wrong?

In my query I had

where startdate<=9/19/2007 and enddate>=9/19/2007

I tried changing the date type around but the results were the same and incorrect.

edit: Am I supposed to use the '@' in front of my date?
Go to Top of Page

lagvoid
Starting Member

8 Posts
Posted - 2007-09-10 : 23:05:03
I have startdate and enddate fields in tblwork. They are date fields.

Example
Startdate is 9/1/06
Enddate is 10/1/06

I want to know who worked on the contract on 9/19/06.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-09-10 : 23:06:07
where startdate<= '20070919' and enddate>= '20070919'

does your column startdate and enddate contain time ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

Zoroaster
Aged Yak Warrior

702 Posts
Posted - 2007-09-10 : 23:06:16
quote:
Originally posted by lagvoid

I tried a query similar to that but it didn't produce the correct results. Maybe my syntax is wrong?

In my query I had

where startdate<=9/19/2007 and enddate>=9/19/2007

I tried changing the date type around but the results were the same and incorrect.


So startdate and enddate are columns in your table? If that is the case and you want a range, such as 9/19/2007 - 9/20/2007 you do startdate >= '9/19/2007' and enddate <= '9/20/2007', what datatype are these fields?


Future guru in the making.
Go to Top of Page

lagvoid
Starting Member

8 Posts
Posted - 2007-09-10 : 23:07:32
yes they contain time, I need to include that as well?

edit:
quote:
So startdate and enddate are columns in your table? If that is the case and you want a range, such as 9/19/2007 - 9/20/2007 you do startdate >= '9/19/2007' and enddate <= '9/20/2007', what datatype are these fields?


Yes they are both columns in the table. I'll have to double check the datatype, I don't remember what it was set to.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-09-10 : 23:23:07
[code]where dateadd(day, datediff(day, 0, startdate), 0) <= '20070919'
and dateadd(day, datediff(day, 0, enddate), 0) >= '20070919'[/code]

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

lagvoid
Starting Member

8 Posts
Posted - 2007-09-10 : 23:26:27
Thank you for all your help! I will try that out.
Go to Top of Page

Zoroaster
Aged Yak Warrior

702 Posts
Posted - 2007-09-10 : 23:29:57
So, to be clear, your table looks similar to this?

startdate | enddate
--------------------
'9/1/06 00:00:00' '10/1/06 00:00:00'
" " " "
" " " "
And you would like to return the rows where a given date is between the startdate and enddate for that row?

If that is true, then for '9/19/06' you could do:

SELECT * FROM tblWork
WHERE startdate > '9/18/06'
AND enddate < '9/20/06'



Future guru in the making.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-09-10 : 23:32:01
I think it will be easier if you can post your table DDL, sample data and required result !

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

lagvoid
Starting Member

8 Posts
Posted - 2007-09-10 : 23:32:37
Yes I'm pretty sure thats exactly what it looks like. I think I tried a query like that but without the quotes '' and it didn't grab the records between the two fields. I thought I tried adding quotes but I'll give that a try as well.

Thanks for helping!

quote:
Originally posted by Zoroaster

So, to be clear, your table looks similar to this?

startdate | enddate
--------------------
'9/1/06 00:00:00' '10/1/06 00:00:00'
" " " "
" " " "
And you would like to return the rows where a given date is between the startdate and enddate for that row?

If that is true, then for '9/19/06' you could do:

SELECT * FROM tblWork
WHERE startdate > '9/18/06'
AND enddate < '9/20/06'



Future guru in the making.
Go to Top of Page

Zoroaster
Aged Yak Warrior

702 Posts
Posted - 2007-09-10 : 23:34:30
quote:
Originally posted by lagvoid

Yes I'm pretty sure thats exactly what it looks like. I think I tried a query like that but without the quotes '' and it didn't grab the records between the two fields. I thought I tried adding quotes but I'll give that a try as well.

Thanks for helping!

quote:
Originally posted by Zoroaster

So, to be clear, your table looks similar to this?

startdate | enddate
--------------------
'9/1/06 00:00:00' '10/1/06 00:00:00'
" " " "
" " " "
And you would like to return the rows where a given date is between the startdate and enddate for that row?

If that is true, then for '9/19/06' you could do:

SELECT * FROM tblWork
WHERE startdate > '9/18/06'
AND enddate < '9/20/06'



Future guru in the making.



No problem, let me know how it goes. I'm off to bed but will be back in the AM.


Future guru in the making.
Go to Top of Page

Koji Matsumura
Posting Yak Master

141 Posts
Posted - 2007-09-10 : 23:52:44
quote:
Originally posted by lagvoid

I have startdate and enddate fields in tblwork. They are date fields.

Example
Startdate is 9/1/06
Enddate is 10/1/06

I want to know who worked on the contract on 9/19/06.


WHERE '20060919' BETWEEN StartDate AND EndDate
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2007-09-11 : 05:49:27
www.sql-server-performance.com/fk_datetime.asp for more info on querying dates

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

lagvoid
Starting Member

8 Posts
Posted - 2007-09-12 : 00:46:20
just wanted to update that it worked. My problem was I was trying to add additional conditions in the 'where' before it and it somehow messed it up. thank you all!
Go to Top of Page

Jenda
Starting Member

29 Posts
Posted - 2007-09-12 : 14:48:08
quote:
Originally posted by khtan

where dateadd(day, datediff(day, 0, startdate), 0) <= '20070919' 
and   dateadd(day, datediff(day, 0, enddate), 0)   >= '20070919'


And if the StartDate or EndDate was indexed you just prevented the server from using that index.

While your code is a nice way to round a datetime to date only I don't see how is that related to the original problem.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site