Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Posts
    3

    Unanswered: Query Error (Rolling 7 days)

    Hi All,

    I am writing the following query inorder to return rolling 7 days.

    SELECT
    day
    FROM
    dates
    WHERE (date(day) between current date-7 days and current date)

    But the following error occurs,

    >[Error] Script lines: 1-5 --------------------------
    Incorrect syntax near the keyword 'current'.
    Msg: 156, Level: 15, State: 2
    Server: SYD, Line: 5 

    Can some one please advice? Appreciate your help in Advance!

    Thank you,

    John

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    select * from 
     --testdata--
    (select dt=dateadd(dd,-number,'20130320')
     from master..spt_values
     where type='P'
     and number<30
    ) testdata --
    where dt between dateadd(dd, datediff(dd, '' ,getdate())-7, '')
      and dateadd(dd, datediff(dd, '' ,getdate()), '')

  3. #3
    Join Date
    Feb 2013
    Posts
    3
    Thank you so much and it is very helpful. Appreciate it.
    Can you please provide me the same for YTD (year-to-date) and (Month-to-date)

    Thank you

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    SELECT 
     first_day_of_year =dateadd(yy, datediff(yy,'',getdate()) ,'')
    ,firs_day_of_month =dateadd(mm, datediff(mm,'',getdate()) ,'')
    ,firs_hour_of_day  =dateadd(dd, datediff(dd,'',getdate()) ,'')
    ,firs_min_of_hour  =dateadd(hh, datediff(hh,'',getdate()) ,'')
    Note: if your dates contain a time portion then don't use between (the default time portion is midnight)
    i.e instead of
    Code:
    where thedate between '20130201' 
                      and '20130228' -- this exclude the 28th with times after midnight
    Rather use
    Code:
    where thedate>='20130201' 
      and thedate< '20130301'
    e.g.
    Code:
    WHERE thedate>=dateadd(mm, datediff(mm,'',getdate())-1 ,'')
      AND thedate< dateadd(mm, datediff(mm,'',getdate())   ,'')

Posting Permissions

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