Returning results between two dates

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

SQL Server Development (2000)

Returning results between two dates

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-05-22 : 04:51:09

Hi

I have a column that stores date as: 21/05/2006 22:57:06

I want to get the results where date is between 21st May and 22nd May.

Now, I am running the following query:


SELECT     *
FROM         Mytable
WHERE     (DateTime > '2006 / 05 / 21') AND (DateTime <= '2006 / 05 / 22')

However, this query does not return the results where Date is 22nd May.

If I change it to: " ..AND (DateTime <= '2006 / 05 / 23') "
Then I get the results where DateTime is 22nd May (but not for 23rd May).

Could you inform me why "...AND (DateTime <= '2006 / 05 / 22') " does not work for retunring data where Date is 22nd May, and how can I solve it?

regards

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-22 : 05:04:15

Try this

SELECT *
FROM Mytable
WHERE (DateTime Between '2006 / 05 / 21' AND '2006 / 05 / 23')

This will give you all the dates from 21/05/2006 00:00:00 to 23/05/2006 00:00:00

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-05-22 : 05:48:24

Thanks
Your query works. However, when I adopt to my situation, it still does not work. I am running my query from a SP:


CREATE PROCEDURE GetDates

@From datetime ,
@To datetime 

Select * from Mytable where  DateTime between @From AND  @To)

Go

From ASP.NET application, I am sending the @From and @To parameters as "5/22/2006".

Even if I run the SP from SQL Query Analyser, I do not get the results for 22nd May:
exec spv_Sitelog_GetSitelog 5,0,'', '2006/05/17' , '2006/05/22'

But the data for 22nd May does exist in my table.

regards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-22 : 06:08:55

does exec GetDates '2006/05/17', '2006/05/22' gives you data for 22nd May ?

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-05-22 : 06:22:44

No. It only returns till 21st May

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-22 : 06:27:02

As what chiragkhabaria has explained.

For exec GetDates '2006/05/17', '2006/05/22'
if you want to include May 22's records

CREATE PROCEDURE GetDates
@From datetime ,
@To datetime 
BEGIN
    Select * from Mytable where DateTime between @From AND (@To + 1))
END

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-05-22 : 06:35:01

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-23 : 02:29:52

http://www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources