none
Selecting records based on month and year parameters

    Question

  • Hi. I have a sql 2008 r2 stored procedure which needs modifying to return the data based on a start / end month and year.

    It's a large SP so I'll summarise - It accepts four parameters:

    @StartMonth NVARCHAR(10)
    @StartYear NVARCHAR(4)
    @EndMonth NVARCHAR(10)
    @EndYear NVARCHAR(4)

    The current WHERE clause is:

    WHERE ta.TimeByDay BETWEEN '01' + '-' + ltrim(LEFT(@StartMonth, 3)) + '-' + @StartYear
    		AND convert(nvarchar,datediff(day, ta.TimeByDay, dateadd(month, 1, ta.TimeByDay))) + '-' + ltrim(LEFT(@EndMonth, 3)) + '-' + @EndYear

    Example of input parameters:

    @StartMonth = N'January',
    		@StartYear = N'2014',
    		@EndMonth = N'February',
    		@EndYear = N'2014',

    Result:

    The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
    
    (1 row(s) affected)
    

    However it executes correctly if we do either of the following:

    1) Run the SQL direct in QA and type in January and February rather than passing in the Start/End Month parameters

    2) As you can see we use the following datediff call to get the number of days per month. IF I replace this with '28', or '31' for example the query also runs (oddly number 1 above then also runs by executing the SP):

    convert(nvarchar,datediff(day, ta.TimeByDay, dateadd(month, 1, ta.TimeByDay)))

    How do I update the WHERE clause to return records between a start/end month and year?

    I'm a day on this so any help appreciated.

    Thanks

    Thursday, July 10, 2014 9:49 AM

Answers

  • How do I update the WHERE clause to return records between a start/end month and year?

    How is declared the ta.TimeByDay column?

    Try

    -- code #1 v3
    ...
    declare @xDate1 datetime, @xDate2 datetime;
    set @xDate1= Convert(datetime, '1' + '-' + LEFT(@StartMonth, 3) + '-' + @StartYear);
    set @xDate2= Convert(datetime, '1' + '-' + LEFT(@EndMonth, 3) + '-' + @EndYear);
    set @xDate2= DateAdd(month, +1, @xDate2);   
    
    ...
    
    WHERE ta.TimeByDay >= @xDate1 and ta.TimeByDay < @xDate2
    
    ...


        José Diz     Belo Horizonte, MG - Brasil


    • Edited by José Diz Thursday, July 10, 2014 11:49 AM v3: w/o ltrim
    • Marked as answer by _c Friday, July 11, 2014 8:12 AM
    Thursday, July 10, 2014 11:34 AM
  • declare @StartMonth NVARCHAR(10),
    @StartYear NVARCHAR(4),
    @EndMonth NVARCHAR(10),
    @EndYear NVARCHAR(4)
    
    Select @StartMonth = N'January',
    		@StartYear = N'2014',
    		@EndMonth = N'February',
    		--@EndMonth = N'December',
    		@EndYear = N'2014'
    
    
    
    
    Select Cast(@StartMonth + '1 ' + @StartYear as date) as beginningofstartdt
    ,Cast(@EndMonth + '1 ' + @EndYear as date) as beginningofenddt
    ,Dateadd(month,1,Cast(@EndMonth + '1 ' + @EndYear as date)) as beginningofNextMonth,
    DateAdd(ms, -3, DateAdd(month, DateDiff(month, 0, Cast(@EndMonth + '1 ' + @EndYear as date))+1, 0) ) endofenddt
    
    
    --Solutions for your question
    
    WHERE ta.TimeByDay >= Cast(@StartMonth + '1 ' + @StartYear as date)
    AND ta.TimeByDay <Dateadd(month,1,Cast(@EndMonth + '1 ' + @EndYear as date))
    
    --Or
    
    WHERE ta.TimeByDay >= Cast(@StartMonth + '1 ' + @StartYear as date)
    AND ta.TimeByDay <=DateAdd(ms, -3, DateAdd(month, DateDiff(month, 0, Cast(@EndMonth + '1 ' + @EndYear as date))+1, 0) )
    
    
    --
    --new functions since SQL Server 2012 Datefromparts, Eomonth ...
    

    • Marked as answer by _c Friday, July 11, 2014 8:12 AM
    Thursday, July 10, 2014 4:36 PM
    Moderator

All replies

  • How do I update the WHERE clause to return records between a start/end month and year?

    How is declared the ta.TimeByDay column?

    Try

    -- code #1 v3
    ...
    declare @xDate1 datetime, @xDate2 datetime;
    set @xDate1= Convert(datetime, '1' + '-' + LEFT(@StartMonth, 3) + '-' + @StartYear);
    set @xDate2= Convert(datetime, '1' + '-' + LEFT(@EndMonth, 3) + '-' + @EndYear);
    set @xDate2= DateAdd(month, +1, @xDate2);   
    
    ...
    
    WHERE ta.TimeByDay >= @xDate1 and ta.TimeByDay < @xDate2
    
    ...


        José Diz     Belo Horizonte, MG - Brasil


    • Edited by José Diz Thursday, July 10, 2014 11:49 AM v3: w/o ltrim
    • Marked as answer by _c Friday, July 11, 2014 8:12 AM
    Thursday, July 10, 2014 11:34 AM
  • Hi Jose - its a datetime column (i.e. 2014-02-03 00:00:00.000).

    Thanks! I'll give that a go.

    Thursday, July 10, 2014 11:50 AM
  • Hi Jose - its a datetime column (i.e. 2014-02-03 00:00:00.000)

    If the time part in ta.TimeByDay column is always reset you can also use

    -- code #2
    ...
    declare @xDate1 datetime, @xDate2 datetime;
    set @xDate1= Convert(datetime, '1' + '-' + LEFT(@StartMonth, 3) + '-' + @StartYear);
    set @xDate2= Convert(datetime, '1' + '-' + LEFT(@EndMonth, 3) + '-' + @EndYear);
    set @xDate2= DateAdd(day, -1, DateAdd(month, +1, @xDate2));   
    
    ...
    
    WHERE ta.TimeByDay between @xDate1 and @xDate2
    
    ...


        José Diz     Belo Horizonte, MG - Brasil


    • Edited by José Diz Thursday, July 10, 2014 11:58 AM
    Thursday, July 10, 2014 11:58 AM
  • create function NthDayOfMonth (@year int, @month smallint, @weekday varchar(15), @nth smallint)
     returns datetime
     as
     begin
     declare @the_date datetime, @c_date datetime, @cth smallint
     set @cth = 0
     set @c_date = convert(varchar,@year)+'-'+convert(varchar,@month)+'-01'
     
      while month(@c_date) = @month
      begin
      if datename(weekday,@c_date) = @weekday set @cth = @cth + 1
      if @cth = @nth and datename(weekday,@c_date) = @weekday set @the_date = @c_date
      set @c_date = dateadd(day,1,@c_date)
      end
    
    return @the_date 
    end
    
    go
    
    create function Dates(@date datetime)
    returns @table table
    (
    now datetime,
    today datetime,
    Month_start datetime,
    Month_end datetime,
    Prev_Month_Start datetime,
    Prev_Month_End datetime,
    Week_Start datetime,
    Week_End datetime,
    Prev_Week_Start datetime,
    Prev_Week_End datetime,
    Quarter_Start datetime,
    Quarter_End datetime,
    Prev_Quarter_Start datetime,
    Prev_Quarter_End datetime,
    Year_Start datetime,
    Year_End datetime,
    Prev_Year_Start datetime,
    Prev_Year_End datetime,
    Month_End_TS datetime,
    Prev_Month_End_TS datetime,
    Week_End_TS datetime,
    Prev_Week_End_TS datetime,
    Quarter_End_TS datetime,
    Prev_Quarter_End_TS datetime,
    Year_End_TS datetime,
    Prev_Year_End_TS datetime,
    Year smallint,
    Month smallint,
    Day smallint,
    Month_Name varchar(15),
    Day_Name varchar(15),
    WD smallint
    )
    as
    
    begin 
    
    if @date IS NULL set @date = getdate()
    
    insert into @table
    
    select
    
    @date as now, 
    convert(datetime,convert(varchar,@date,101)) as today,
    
    dateadd(day,0-day(@date)+1,convert(datetime,convert(varchar,@date,101))) as Month_Start,
    dateadd(day,-1,dateadd(month,1,dateadd(day,0-day(@date)+1,convert(datetime,convert(varchar,@date,101))))) as Month_end,
    dateadd(month,-1,dateadd(day,0-day(@date)+1,convert(datetime,convert(varchar,@date,101)))) as Prev_Month_start,
    dateadd(day,-1-day(@date)+1,convert(datetime,convert(varchar,@date,101))) as Prev_Month_End,
    
    dateadd(day,1-datepart(dw,@date),convert(datetime,convert(varchar,@date,101))) as Week_Start,
    dateadd(day,7-datepart(dw,@date),convert(datetime,convert(varchar,@date,101))) as Week_End,
    dateadd(day,-6-datepart(dw,@date),convert(datetime,convert(varchar,@date,101))) as Prev_Week_Start,
    dateadd(day,0-datepart(dw,@date),convert(datetime,convert(varchar,@date,101))) as Prev_Week_End,
    
    convert(datetime,convert(varchar,year(@date)) +'-'+ right('0'+convert(varchar,((datepart(QUARTER,@date)-1)*3)+1),2)+'-01') as quarter_start,
    dateadd(day,-1,dateadd(quarter,1,convert(datetime,convert(varchar,year(@date)) +'-'+ right('0'+convert(varchar,((datepart(QUARTER,@date)-1)*3)+1),2)+'-01'))) as quarter_end,
    convert(datetime,convert(varchar,year(dateadd(quarter,-1,@date))) +'-'+ right('0'+convert(varchar,((datepart(QUARTER,dateadd(quarter,-1,@date))-1)*3)+1),2)+'-01') as prev_quarter_start,
    dateadd(day,-1,dateadd(quarter,1,convert(datetime,convert(varchar,year(dateadd(quarter,-1,@date))) +'-'+ right('0'+convert(varchar,((datepart(QUARTER,dateadd(quarter,-1,@date))-1)*3)+1),2)+'-01'))) as prev_quarter_end,
    
    dateadd(day,1-day(@date),dateadd(month,1-month(@date),convert(varchar,@date,101))) as Year_Start,
    dateadd(year,1,dateadd(day,0-day(@date),dateadd(month,1-month(@date),convert(varchar,@date,101)))) as Year_End,
    
    dateadd(year,-1,dateadd(day,1-day(@date),dateadd(month,1-month(@date),convert(varchar,@date,101)))) as Prev_Year_Start,
    dateadd(year,-1,dateadd(year,1,dateadd(day,0-day(@date),dateadd(month,1-month(@date),convert(varchar,@date,101))))) as Prev_Year_End,
    
    dateadd(ms,-3,dateadd(day,0,dateadd(month,1,dateadd(day,0-day(@date)+1,convert(datetime,convert(varchar,@date,101)))))) as Month_End_Ts,
    dateadd(ms,-3,dateadd(day,-1-day(@date)+2,convert(datetime,convert(varchar,@date,101)))) as Prev_Month_End_TS,
    dateadd(ms,-3,dateadd(day,8-datepart(dw,@date),convert(datetime,convert(varchar,@date,101)))) as Week_End_TS,
    dateadd(ms,-3,dateadd(day,1-datepart(dw,@date),convert(datetime,convert(varchar,@date,101)))) as Prev_Week_End_TS,
    
    dateadd(ms,-3,dateadd(day,0,dateadd(quarter,1,convert(datetime,convert(varchar,year(@date)) +'-'+ right('0'+convert(varchar,((datepart(QUARTER,@date)-1)*3)+1),2)+'-01')))) as quarter_end_TS,
    dateadd(ms,-3,dateadd(day,0,dateadd(quarter,1,convert(datetime,convert(varchar,year(dateadd(quarter,-1,@date))) +'-'+ right('0'+convert(varchar,((datepart(QUARTER,dateadd(quarter,-1,@date))-1)*3)+1),2)+'-01')))) as prev_quarter_end_TS,
    
    
    dateadd(ms,-3,dateadd(year,1,dateadd(day,1-day(@date),dateadd(month,1-month(@date),convert(varchar,@date,101))))) as Year_End_TS,
    dateadd(ms,-3,dateadd(day,1-day(@date),dateadd(month,1-month(@date),convert(varchar,@date,101)))) as Prev_Year_End_TS,
    
    Year(@date) as Year,
    Month(@date) as Month,
    Day(@Date) as Day,
    datename(month,@Date) as Month_Name,
    datename(WEEKDAY,@date) as Day_Name,
    datepart(weekday,@date) as WD
    
    return 
    
    end
    
    go
    
    
    create function Holidays(@year smallint)
    returns @table table
    (
     date datetime,
     type varchar(10),
     name varchar(25)
    )
    as
    begin
    insert into @table
    select convert(datetime,convert(varchar,@year)+'-01-01') as date,'Holiday' as type					  ,'New Years Day' as name UNION ALL
    select dbo.NthDayOfMonth(@year,2, 'Monday',3),'Holiday','Family Day'      UNION ALL
    select dateadd(d,0-case when datepart(weekday,convert(varchar,@year)+'-05-25') in (1,2) then 5+datepart(weekday,convert(varchar,@year)+'-05-25') else datepart(weekday,convert(varchar,@year)+'-05-25')-1 end, convert(varchar,@year)+'-05-25') ,'Holiday','Victoria Day'    UNION ALL
    select convert(varchar,@year)+'-01-07'                         ,'Holiday','Canada Day'      UNION ALL
    select dbo.NthDayOfMonth(@year,8, 'Monday',1),'Holiday','Civic Holiday'   UNION ALL
    select dbo.NthDayOfMonth(@year,9, 'Monday',1),'Holiday','Labour Day'      UNION ALL
    select dbo.NthDayofMonth(@year,10,'Monday',2),'Holiday','Thanksgiving'    UNION ALL
    select convert(varchar,@year)+'-11-11'                         ,'Holiday','Rememberance Day'UNION ALL
    select convert(varchar,@year)+'-12-25'                         ,'Holiday','Christmas Day'   UNION ALL
    select convert(varchar,@year)+'-12-26'                         ,'Holiday','Boxing Day' 
    
    update @table
    set date = 
    case when name != 'Boxing Day' and datepart(weekday,date) = 7 then dateadd(day,2,date)
         when name != 'Boxing Day' and datepart(weekday,date) = 1 then dateadd(day,1,date)
    	 when name = 'Boxing Day' and datepart(weekday,date) = 7 then dateadd(day,2,date)
    	 when name = 'Boxing Day' and datepart(weekday,date) = 1 then dateadd(day,2,date)
    	 when name = 'Boxing Day' and datepart(weekday,date) = 2 then dateadd(day,1,date)
    	 else date
    end 
    
    
    return 
    
    end
    
    go
    
    
    

    Using these functions (in place of a calendar table) you could do something like this:

    DECLARE @forumTable TABLE (sales MONEY, saleDate DATE)
    INSERT INTO @forumTable (sales, saleDate)
    VALUES
    (123.45, '2014-01-05'),(678.90, '2014-01-06'),(111.21, '2014-01-07'),(314.15, '2014-01-08'),(161.71, '2014-01-09'),
    (819.20, '2014-02-05'),(212.22, '2014-02-06'),(324.25, '2014-02-07'),(262.72, '2014-02-08'),(829.30, '2014-02-09')
    
    SELECT SUM(f.sales), d.month_end
      FROM @forumTable f
        CROSS APPLY sandbox.dbo.dates(f.saleDate) d
    GROUP BY d.month_end


    Thursday, July 10, 2014 2:21 PM
  • declare @StartMonth NVARCHAR(10),
    @StartYear NVARCHAR(4),
    @EndMonth NVARCHAR(10),
    @EndYear NVARCHAR(4)
    
    Select @StartMonth = N'January',
    		@StartYear = N'2014',
    		@EndMonth = N'February',
    		--@EndMonth = N'December',
    		@EndYear = N'2014'
    
    
    
    
    Select Cast(@StartMonth + '1 ' + @StartYear as date) as beginningofstartdt
    ,Cast(@EndMonth + '1 ' + @EndYear as date) as beginningofenddt
    ,Dateadd(month,1,Cast(@EndMonth + '1 ' + @EndYear as date)) as beginningofNextMonth,
    DateAdd(ms, -3, DateAdd(month, DateDiff(month, 0, Cast(@EndMonth + '1 ' + @EndYear as date))+1, 0) ) endofenddt
    
    
    --Solutions for your question
    
    WHERE ta.TimeByDay >= Cast(@StartMonth + '1 ' + @StartYear as date)
    AND ta.TimeByDay <Dateadd(month,1,Cast(@EndMonth + '1 ' + @EndYear as date))
    
    --Or
    
    WHERE ta.TimeByDay >= Cast(@StartMonth + '1 ' + @StartYear as date)
    AND ta.TimeByDay <=DateAdd(ms, -3, DateAdd(month, DateDiff(month, 0, Cast(@EndMonth + '1 ' + @EndYear as date))+1, 0) )
    
    
    --
    --new functions since SQL Server 2012 Datefromparts, Eomonth ...
    

    • Marked as answer by _c Friday, July 11, 2014 8:12 AM
    Thursday, July 10, 2014 4:36 PM
    Moderator
  • Thank you all for your solutions!
    Friday, July 11, 2014 8:12 AM
  • Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML.

    The report period table gives a name to a range of dates that is common to the entire enterprise. 

    CREATE TABLE Something_Report_Periods
    (something_report_name CHAR(10) NOT NULL PRIMARY KEY
       CHECK (something_report_name LIKE <pattern>),
     something_report_start_date DATE NOT NULL,
     something_report_end_date DATE NOT NULL,
      CONSTRAINT date_ordering
        CHECK (something_report_start_date <= something_report_end_date),
    etc);

    These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantages are that it will sort with the ISO-8601 data format required by Standard SQL and it is language independent. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[01][0-9]-00'

    The people who used those Microsoft proprietary functions have given you awful advice and have no idea how to write SQL.

     

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, July 11, 2014 2:44 PM