Retrieve data based on date - 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

SQL Server Development (2000)

Retrieve data based on date

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-03 : 10:01:11

Johnson writes "How would I retrieve data based on date range?if d field is smalldatetime and system generated date,meaning the field has date and time. coz this is what i get,for ex. if i entered dates from april 1,2001 to april 20,2001, it will only return records from april 1 to 19,2001"

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-03 : 10:08:55

To retrive data based on date ranges you can use BETWEEN or >= AND <=

Example from BOL


SELECT title_id, ytd_sales
FROM titles
WHERE ytd_sales BETWEEM 4095 AND 12000

for dates you just do the same

SELECT *
FROM table1
WHERE datefield BETWEEN '04/01/02' AND '04/20/02'

I believe than that the 04/20/02 date will default to a time of 00:00:00. Make sure that the time values in the DB are this as well. If they arent then you will have to make sure you either increase the dates are use DateFunctions to just return the date and use the BETWEEN function on that.

robvolk - Sorry yakoo, had to edit this, the code tag was messing up the display.

Edited by - robvolk on 04/03/2002 10:56:21

dsdeming

479 Posts

Posted - 2002-04-03 : 13:01:06

The reason you're only getting data 'from april 1 to 19,2001' is that when you use between with a date range where the time is not being used, you need to add 1 day and subtract 1 second from the upper limit:

WHERE @date BETWEEN '4/1/2001'
AND DATEADD( ss, -1, DATEADD( dd, 1, '4/20/2001' ))

This will give you everything from 4/1/2001 00:00:00 thru 4/20/2001 23:59:59.

Traber
Starting Member

2 Posts

Posted - 2002-04-03 : 14:54:49

Simplest way:

select *
from Table1
where '4/1/2001' <= DateField and DateField < '4/21/2001'

Obviously it doesn't use between and lists the field twice, but between is a shortcut for (@1 <= Field and Field <= @2) anyway.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources