Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Question Unanswered: Using dynamic date ranges

    I have a sql query that give me the output of set of data based upon the date.

    select * from reports where date <= '10/30/2003' and date >= '10/01/2003')

    Any way I can have the query pull a specific date period (monthly 30 day period)

    say select * from reports where date <= '30 day period I specify' and date >= '30 period I specify')

    thanks for any help

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it's possible

    how did you want to specify the 30 day period?

    because if you specify it using dates, then that is not really different from the way you've already got it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163
    Originally posted by r937
    yes, it's possible

    how did you want to specify the 30 day period?

    because if you specify it using dates, then that is not really different from the way you've already got it
    The best way (i think) it's create the a stored procedure that receive the year and the month and then it will automatically count 30/31 days deppending on the month

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is one way:

    declare @PeriodStart datetime
    set @PeriodStart = '10/01/2003'

    select *
    from reports
    where datediff(d, @StartDate, [date]) between 0 and 30

    blindman

  5. #5
    Join Date
    Oct 2003
    Posts
    7
    Originally posted by r937
    yes, it's possible

    how did you want to specify the 30 day period?

    because if you specify it using dates, then that is not really different from the way you've already got it

    I wanted to specify the ranges using dates. I have a date table in a datetime format and I was trying to get a script via asp to pull selected vaules based upon the date that was entered into the form.

    Say show me the records for all of september via text box or drop down and have the entered values placed in the sql query string.

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Originally posted by blindman
    Here is one way:

    declare @PeriodStart datetime
    set @PeriodStart = '10/01/2003'

    select *
    from reports
    where datediff(d, @StartDate, [date]) between 0 and 30

    blindman
    Would this solution force a table scan? I was thinking to create a second variable = dateadd(d,30,@PeriodStart) and then use the two variables with the between operator.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Oct 2003
    Posts
    7
    Originally posted by blindman
    Here is one way:

    declare @PeriodStart datetime
    set @PeriodStart = '10/01/2003'

    select *
    from reports
    where datediff(d, @StartDate, [date]) between 0 and 30

    blindman
    Blindman, thanks for your query example.
    I ran it with my values and it returns exactly what I am looking for. But I changed @Startdate with @PeriodStart and it gave me values between 0-30. What would I need to put in for the variable @Startdate to be taken from a user input form submission?

    Example for the form: Input the 30-31 date ranges for wich you would like to view the results.
    So would they input 10/01/2003 as start date and 10/31/2003 as end date? Then pass this result as the variable into the @startdate? Thanks

  8. #8
    Join Date
    Oct 2003
    Posts
    7
    Originally posted by zosimov
    Blindman, thanks for your query example.
    I ran it with my values and it returns exactly what I am looking for. But I changed @Startdate with @PeriodStart and it gave me values between 0-30. What would I need to put in for the variable @Startdate to be taken from a user input form submission?

    Example for the form: Input the 30-31 date ranges for wich you would like to view the results.
    So would they input 10/01/2003 as start date and 10/31/2003 as end date? Then pass this result as the variable into the @startdate? Thanks

    Ok

    Never mind that here is what I am doing. Hope this works

    initialdate=Request.Form("initialdate")
    finaldate=Request.Form("finaldate")

    sqltext = "select * from REPORTS where [date] >= '" & initialdate & "' and [date] <= (dateadd(d,1,'" & finaldate _
    & "'))"
    teDeliver"))

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Paul,
    Yes, I think your variation would be more efficient.

    zosimov
    Why are you adding 1 to the finaldate? Is it to cover up to midnight of that date? In that case you would want to use a straight < operator rather than <=, which would include midnight:

    sqltext = "select * from REPORTS where [date] >= '" & initialdate & "' and [date] < dateadd(d, 1, '" & finaldate & "')"

    Otherwise, use the BETWEEN operator instead:

    sqltext = "select * from REPORTS where [date] between '" & initialdate & "' and '" & finaldate & "'"

    Also,
    "show me the records for all of september" is of course NOT the same as show me a 30 day period unless you limit your parameters to September, April, June, and November, because all the rest have 31, except February...blah blah blah

    If you want to select or group by a month at a time, you are better off truncating your date values to the first of the month and comparing on the result:

    cast(convert(varchar(7), [YourDate], 120) + '/01') as datetime

    This will allow you to look at whole months, rather than fixed 30-day spans.

    blindman

  10. #10
    Join Date
    Sep 2003
    Posts
    522
    create two functions that accept a date and run your routine by passing a date in the where clause:

    Code:
    create function dbo.fn_FirstDateOfMonth (
       @date datetime ) returns datetime
    as begin
       declare @DateTime datetime, @Month int, @Year int, @MonthString char(2), @YearString char(4)
       set @Month = datepart(mm, @date)
       set @Year = datepart(yy, @date)
       if @Month < 10
          set @MonthString = '0' + cast(@Month as varchar(2))
       else
          set @MonthString = cast(@Month as varchar(2))
       set @YearString = cast(@Year as varchar(4))
       set @DateTime = cast(@MonthString + '/01/' + @YearString as datetime)
       return @DateTime
    end
    
    create function dbo.fn_LastDateOfMonth (
       @date   datetime ) returns datetime
    as begin
       return (
          dateadd(day, -1, 
             dateadd(month, 1,
                case
                   when datepart(month, @date) < 10 then '0' + cast(datepart(month, @date) as varchar(2))
                   else cast(datepart(month, @date) as varchar(2))
                end + '/01/' + cast(datepart(year, @date) as varchar(10))
             )
          )
       )
    end

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ms_sql_dba, these functions are certainly handy to have in any database (beats me why they aren't standard), but I think your code is more complicated than necessary.

    create function dbo.fn_FirstDateOfMonth (@date datetime)
    returns datetime
    as
    begin
    return cast(convert(varchar(7), @date, 120) + '/01' as datetime)
    end

    create function dbo.fn_LastDateOfMonth (@date datetime)
    returns datetime
    as
    begin
    return dateadd(d, -1, dateadd(m, 1, cast(convert(varchar(7), @date, 120) + '/01' as datetime)))
    end

    blindman

  12. #12
    Join Date
    Sep 2003
    Posts
    522
    hey, whatever works for you

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No one works for me. That's why I have to do all the work myself!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •