Select Range Of Date

  • 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.

  • 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-

  • 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

  • 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

  • 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

  • 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

  • thanks ripg1011

    that did the trick.

    I had my < & > inversed

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

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