how to retrieve records between 2 dates? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

how to retrieve records between 2 dates?

Author

Topic

kapinjal
Starting Member

2 Posts

Posted - 2002-03-14 : 03:22:00

hi ppl,

i got 2 tables "purchase" & "product"

fields in :

purchase: purchid,fk_prodid,created,amount,receipt

product: prodid, prodname,price

fk_prodid is a foreign key to product table..

and i m trying to fetch the records e.g

prodname,puchdate,amount,receipt between 2 particular date selected by user!!

i m able to to join the table but when i try to use between for those 2 dates selected!
it gives me all the records!!

"created" field in purchases table is a datetime datatype which holds value from getdate() functions which is stored something like 14/03/2002 12:23:45 PM

and even if try to query that

select * from purchases where created = 14/03/2002

it doesnt return any value!!

or if i can format that getdate() which stores something like 14/03/2002 instead of 14/03/2002 12:23:45 PM
it will make it easier..

any help out there?

regards

kaps

Nazim
A custom title

1408 Posts

Posted - 2002-03-14 : 03:42:00

you can use between stardate and enddate

eg:

select pr.prodname,puchdate,amount,receipt from product pr
inner join purchase pu
on pr.productid=pu.productid
where created between '14/03/2002 12:23:45 PM' and '20/03/2002 12:23:45 PM'

and if you dont care about the time check for Cast and Convert in BOL.

--------------------------------------------------------------

Edited by - Nazim on 03/14/2002 03:45:05

dsdeming

479 Posts

Posted - 2002-03-14 : 08:08:13

If you're building a BETWEEN in a WHERE clause based on user-selected dates, keep this in mind. If your user wants all rows created between the 1st and 5th of March, and you select WHERE Created BETWEEN '3/1/2002' AND '3/5/2002', what you have really asked for is WHERE Created BETWEEN '3/1/2002 00:00:00' AND '3/5/2002 00:00:00'. Only rows created in the 1st second of March 5th will be returned. This is probably not what the user was after. The usual way around this is to add a day and subtract a second from the higher date.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources