SQL Question on how to calculate a beginning and ending date

Answers

• 

  

  0

  Sign in to vote

  You may want to think about creating a lookup table that contains the third Sunday of each month for the past 20 years instead of calculating it on the fly in your script/stored procedure if you'll be using this frequently.

  Ex:
  Year    Month  ThirdSaturday
  2012   6          06/16/2012
  2012   7          07/21/2012
  2012   8          08/18/2012

  Using the DATEADD function, you could calculate the "start date".  Here is a simple example of DATEADD where you subtract 5 days from the current date:

  DECLARE @d DATETIME = GETDATE();
  SELECT DATEADD(DAY,-5,@d)
  

  If you do not want to use a lookup table to store the dates, there are several good discussions online on how to find the Nth day/week of the month. 
  http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx

  Thanks,
  Sam Lester (MSFT)
  

  ---

  My Blog

  This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

  • Proposed as answer by Samuel Lester - MSFTMicrosoft employee Saturday, August 11, 2012 3:45 PM
  • Marked as answer by Kalman Toth Friday, August 17, 2012 4:45 AM

  Saturday, August 11, 2012 3:44 PM
• 

  

  0

  Sign in to vote

  Declare @Year Int, @Month Int, @EndDate Date,@StartDate Date, @DayName Varchar(25), @NumberOfDays Int, @NthDay Int
  
  Select @Year = 2012	/*YEAR you want to perform*/
  Select @Month = 8 /*MONTH you want to perform*/
  Select @DayName = 'Saturday' /*WEEKDAY you want to perform*/
  Select @NumberOfDays = 45 /*DAYS you want to perform*/
  Select @NthDay = 3 /*Nth WEEKDAY you want to perform*/
  
  ;With CTEs
  As
  (
  Select 0 Slno
  Union All
  Select Slno + 1  From CTEs Where Slno <=30
  ),[Days]
  As
  (
  Select DateName(Weekday,DATEADD(Day,Slno,Cast(@Year as varchar)+ '-' + Cast(@Month as varchar) + '-01')) [Day], DATEADD(Day,Slno,Cast(@Year as varchar)+ '-' + Cast(@Month as varchar) + '-01') [Date] From CTEs
  ),[DayWithSeq]
  As
  (
  Select ROW_NUMBER() Over(Order By [Date]) Nth,* from [Days] Where Month([Date]) =@Month And [Day] = @DayName
  )
  
  Select @StartDate = [Date] - @NumberOfDays , @EndDate = [Date]  from [DayWithSeq] Where Nth = @NthDay
  
  /*Start and End Date to be performed*/
  Select @StartDate [StartDate], @EndDate [EndDate]

  Result:

  StartDate           EndDate
  ------------         -----------
  2012-07-04	  2012-08-18

  Now, you can use the StartDate and EndDate in your Query (i.e: BETWEEN or >= and <= operators to validate the daterange)

  • Edited by SQLServerBuddy Saturday, August 11, 2012 3:48 PM
  • Marked as answer by Kalman Toth Friday, August 17, 2012 4:45 AM

  Saturday, August 11, 2012 3:46 PM
• 

  

  0

  Sign in to vote

  Assuming the date provided always the third Saturday of the month, you can use DATEADD to calcuate the starting date.  For the date range, you might consider an inclusive start date and and exclusive end date so that the query will return the expected results even if the inventory date is a datetime or datetime2 column with a time component other than midnight.  Stored procedure example:

  CREATE PROC dbo.GetDailyInventory
  	@EndDate datetime
  	,@NumberOfDays int
  AS
  DECLARE @StartDate datetime;
  
  --calculate inclusive start date and exclusive end date
  SELECT
  	@StartDate = DATEADD(day, -@NumberOfDays, @EndDate)
  	,@EndDate = DATEADD(day, 1, @EndDate);
  
  SELECT InventoryDate, QuantityOnHand
  FROM dbo.Inventory
  WHERE 
  	InventoryDate >= @StartDate
  	AND InventoryDate < @EndDate
  ORDER BY InventoryDate;
  RETURN @@ERROR;
  GO
  

  If the third Saturday date needs to be calcuated, how will the desired month be specified (year and month, first date of month, last date of month)?
  

  ---

  Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

  • Marked as answer by Kalman Toth Friday, August 17, 2012 4:44 AM

  Saturday, August 11, 2012 3:48 PM
• 

  

  0

  Sign in to vote

  It sounds like you need a table of Numbers. You can read about this concept here:
  http://www.sommarskog.se/arrays-in-sql-2005.html#numbersasconcept
  (Only read down the header "An Inline Function".)

  Once you have that table, you can produces all the desired Saturdays this way:

  WITH CTE AS (
     SELECT convert(date,
              convert(varchar, y.Number) +
              substring(convert(varchar, m.Number), 2, 3) +
              convert(varchar, d.Number)) AS date
     FROM   Numbers y
     CROSS  JOIN Numbers m
     CROSS  JOIN Numbers d
     WHERE  y.Number BETWEEN 1992 AND 2012
       AND  m.Number BETWEEN 101 AND 112
       AND  d.Number BETWEEN 15 AND 21
  )
  SELECT date
  FROM   CTE
  WHERE  datename(weekday, date) = 'Saturday'
  ORDER BY date

  ---

  Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

  • Marked as answer by Kalman Toth Friday, August 17, 2012 4:44 AM

  Saturday, August 11, 2012 9:07 PM
• 

  

  0

  Sign in to vote

  A useful idiom is a report period calendar. It gives a name to a range of dates. 
  
  CREATE TABLE Report_Periods
  (report_name CHAR(10) NOT NULL PRIMARY KEY,
   report_start_date DATE NOT NULL,
   report_end_date DATE NOT NULL,
   CONSTRAINT date_ordering
   CHECK (report_start_date <= report_end_date),
   etc);
  
  These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can be gaps between them; we have to wait a year between each “Annual Going out Of Business Sale!” and there might be long stretches of time without any special sales. But we want to know their ranges so that the table is fairly constant once it is created. 

  Also, start using the ISO-8601 date format (yyyy-mm-dd); it is the only format allowed in Standard SQ as  well as most ISO Standards and MS is moving to support it in the new temporal feaures.  

  ---

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

  • Marked as answer by Kalman Toth Friday, August 17, 2012 4:44 AM

  Saturday, August 11, 2012 9:15 PM