Select records between 2 months/years

Question

Select records between 2 months/years

amfps

SSC Eights!

Points: 864
More actions
October 20, 2003 at 4:18 am

#82450

Hello,
I have a table and all records have a date.
I want to select all records between two given months/years.
For example, all records between 01/04 to 05/05.
How can I do this?
Thank you.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi,

quote:
I have a table and all records have a date.
I want to select all records between two given months/years.
For example, all records between 01/04 to 05/05.
How can I do this?

without knowing your exact table structure I would guess something like

WHERE your_datefield BETWEEN start_date AND end_date

BOL has further explanation

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

buddy__a Hall of Fame Points: 3222 More actions · Answer 2

quote:
I have a table and all records have a date.
I want to select all records between two given months/years.
For example, all records between 01/04 to 05/05.
How can I do this?

So what do you mean by between for a month. When you say between 01/04 to 05/05 do you really mean from 1/1/04 to 5/31/05 (inclusive) or do you mean 2/1/04 to 4/30/05 (inclusive) or do you mean something else? Either way you will have to add the day to your dates to use Frank's solution. Also, you need to remember that dates are full date and time so if you store dates with the time (by using getdate() when you insert the record) you will need to adjust the ending date so that it includes all times on that day up to 23:59:59.999 on that day.

The following statment will not get all records with a datetime value after 12AM on 5/31/03:

SELECT * FROM myTable WHERE myDateField between '1/1/04' and '5/31/05'

This statment will get all of the records on 5/31/05:

SELECT * FROM myTable

WHERE myDateField >= '1/1/04' and

myDateField < dateadd(dy, 1, '5/31/05')

This is because '5/31/05' = 2005-05-31 00:00:00.000

--Buddy

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Is the original question answered?

Maybe we can get a feedback?

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

amfps SSC Eights! Points: 864 More actions · Answer 4

Thank you for your help.

I used buddy_a's solution, but with a small change. Since not all months have 30 or 31 days, I do...

"SELECT * FROM myTable WHERE myDateField >= '1/1/04' and myDateField < '6/1/05'"

I believe these solves all my problems (in this matter).

Thank you