Select Range Of Date

Question

Select Range Of Date

mightyjoemad

Mr or Mrs. 500

Points: 586
More actions
September 17, 2003 at 8:20 am

#83173

This is probably simple to do but for some reason I cant get it.
1- I have a date table that has a start quarter and an end quarter.
2- I select a date from another table and I need to check in what quarter range does this date falls into the start quarter and end quarter and then select the start date and end date.

Viewing 7 posts - 1 through 6 (of 6 total)

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

guarddata SSCertifiable Points: 6583 More actions · Answer 1

I don't understand the whole issue. Seems like using <DATEPART( quarter, <somedate> ) > is going to be helpful. Could you explain more about "then select the start date and end date"? Do you mean you need the first day of the quarter and the last?

Guarddata-

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 2

Not really sure you even need to use the quarter table, provided you have normal quarter boundaries. If you have normal boundaries then I might use something like this to calculate the quarter start and end dates:

-- first second of current quarter

select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

-- Last second of current quarter

select dateadd(ms,-3,dateadd(qq,1,DATEADD(qq, DATEDIFF(qq,0,getdate() ), 0)))

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 3

hi, a very simple solution :-

create table quarter_dates

(start_date datetime, end_date datetime)

insert into quarter_dates

values ("April 1 2003", "June 30 2003")

insert into quarter_dates

values ("January 1 2003", "March 31 2003")

declare @date datetime

select @date="Feb 21 2003"

select start_date, end_date

from quarter_dates

where @date > start_date and @date < end_date

HTH

Paul

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 4

hi, a very simple solution :-

create table quarter_dates

(start_date datetime, end_date datetime)

insert into quarter_dates

values ("April 1 2003", "June 30 2003")

insert into quarter_dates

values ("January 1 2003", "March 31 2003")

declare @date datetime

select @date="Feb 21 2003"

select start_date, end_date

from quarter_dates

where @date > start_date and @date < end_date

HTH

Paul

DAVNovak Hall of Fame Points: 3120 More actions · Answer 5

I agree with Paul's solution except if your date falls on the exact start or end of a quarter. To fix this you have to do two things. First, make sure the start and end dates in the Quarters table do not overlap or share a common date. Second, modify the Paul's WHERE statement to as follows:

where @date >= start_date and @date <= end_date

mightyjoemad Mr or Mrs. 500 Points: 586 More actions · Answer 6

thanks ripg1011

that did the trick.

I had my < & > inversed