Post reply

Select Range Of Date

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

  • September 17, 2003 at 8:30 am

    #474650

    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-

  • September 17, 2003 at 8:32 am

    #474652

    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

  • September 17, 2003 at 8:35 am

    #474654

    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

  • September 17, 2003 at 8:35 am

    #474653

    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

  • September 17, 2003 at 9:45 am

    #474673

    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

  • September 17, 2003 at 10:54 am

    #474692

    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