locked
Calculating next date in custom format based on past date and weekly / monthly parameters RRS feed

  • Question

  • Hi everyone,

    I need to find a way to calculate an upcoming date, and present it in custom format (SQL Server Express 2008).

    For week starting 30 November 2014 here are two examples:

    1) When field Frequency = WEEKLY and field FreqDet = TUESDAY (Tuesday being 2nd day of week) --> Expected result should be = TUESDAY 2 DEC 2014.

    2) Frequency = MONTHLY and FreqDet = 28 --> Expected result should be = SUNDAY 28 DEC 2014.

    Any ideas, tips, or examples will be more than welcome. Thanks in advance! 


    Gabriel A. Cánepa

    Monday, February 23, 2015 2:05 PM

Answers

  • As it stands, the code will handle any number in MONTHLY. It just adds one month to the start date, and sets the day to the value.

    This could be improved by checking that the day is less than the day from start:

    DECLARE @table TABLE (frequency VARCHAR(10), FreqDet VARCHAR(10))
    INSERT INTO @table (frequency, FreqDet) VALUES
    ('WEEKLY','Sunday'),('WEEKLY','Monday'),('WEEKLY','Tuesday'),
    ('Monthly','28'),('Monthly','2'),('Monthly','27'),('Monthly','9'),('Monthly','12')
    
    DECLARE @date DATETIME = '2014-11-16'
    
    SELECT *,
    CASE WHEN frequency = 'WEEKLY' THEN
         CASE WHEN FreqDet = 'Sunday'  THEN DATEADD(DAY,1-DATEPART(WEEKDAY, @date),@date)
    	      WHEN FreqDet = 'MONDAY'  THEN DATEADD(DAY,2-DATEPART(WEEKDAY, @date),@date)
              WHEN FreqDet = 'TUESDAY' THEN DATEADD(DAY,3-DATEPART(WEEKDAY, @date),@date) 
    	 END
    	 WHEN frequency = 'Monthly' THEN
    	 CASE WHEN FreqDet <= DAY(@date) THEN CAST(YEAR(DATEADD(MONTH,1,@DATE)) AS VARCHAR)+'-'+CAST(MONTH(DATEADD(MONTH,1,@DATE)) AS VARCHAR)+'-'+FreqDet
    	      ELSE CAST(YEAR(@DATE) AS VARCHAR)+'-'+CAST(MONTH(@DATE) AS VARCHAR)+'-'+FreqDet
    	END
    END
      FROM @table

    Monday, February 23, 2015 3:57 PM

All replies

  • First of all, formatting should not be done in your database layer. That's a task for your presentation layer.
    Dynamically calculating a date from a date object is fairly straight forward.

    DECLARE @table TABLE (frequency VARCHAR(10), FreqDet VARCHAR(10))
    INSERT INTO @table (frequency, FreqDet) VALUES
    ('WEEKLY','Tuesday'),
    ('Monthly','28')
    
    DECLARE @date DATETIME = '2014-11-30'
    
    SELECT *,
    CASE WHEN frequency = 'WEEKLY' THEN
         CASE WHEN FreqDet = 'MONDAY'  THEN DATEADD(DAY,2-DATEPART(WEEKDAY, @date),@date)
              WHEN FreqDet = 'TUESDAY' THEN DATEADD(DAY,3-DATEPART(WEEKDAY, @date),@date) 
    	 END
    	 WHEN frequency = 'Monthly' THEN
    	 CAST(YEAR(DATEADD(MONTH,1,@DATE)) AS VARCHAR)+'-'+CAST(MONTH(DATEADD(MONTH,1,@DATE)) AS VARCHAR)+'-'+FreqDet
    END
      FROM @table
    

    Monday, February 23, 2015 2:48 PM
  • As noted above, temporal data should be stored as DATETIME or DATE in the database.

    DATETIME/DATE can be converted to zillion string date formats:

    http://www.sqlusa.com/bestpractices/datetimeconversion/




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014




    Monday, February 23, 2015 2:52 PM
  • @Patrick,

    You're right about formatting the date in the presentation layer. I totally missed that. 

    I forgot to explain that valid values for FreqDet are MONDAY through SUNDAY if Frequency = WEEKLY and 1 through 28 if Frequency = MONTHLY.

    Based on your example, I believe I can add a few CASEs and cover the chance of Frequency = WEEKLY. However, I don't want to add 28 cases to cover all the possibilities of Frequency = MONTHLY.

    If there is a way, do you have any ideas on how I can parametrize the query a little bit more in order to cover the 2 possibilities (Frequency = WEEKLY or Frequency = MONTHLY) in as few lines as possible?

    @Kalman,

    Thank you for the link!


    Gabriel A. Cánepa

    Monday, February 23, 2015 2:58 PM
  • As it stands, the code will handle any number in MONTHLY. It just adds one month to the start date, and sets the day to the value.

    This could be improved by checking that the day is less than the day from start:

    DECLARE @table TABLE (frequency VARCHAR(10), FreqDet VARCHAR(10))
    INSERT INTO @table (frequency, FreqDet) VALUES
    ('WEEKLY','Sunday'),('WEEKLY','Monday'),('WEEKLY','Tuesday'),
    ('Monthly','28'),('Monthly','2'),('Monthly','27'),('Monthly','9'),('Monthly','12')
    
    DECLARE @date DATETIME = '2014-11-16'
    
    SELECT *,
    CASE WHEN frequency = 'WEEKLY' THEN
         CASE WHEN FreqDet = 'Sunday'  THEN DATEADD(DAY,1-DATEPART(WEEKDAY, @date),@date)
    	      WHEN FreqDet = 'MONDAY'  THEN DATEADD(DAY,2-DATEPART(WEEKDAY, @date),@date)
              WHEN FreqDet = 'TUESDAY' THEN DATEADD(DAY,3-DATEPART(WEEKDAY, @date),@date) 
    	 END
    	 WHEN frequency = 'Monthly' THEN
    	 CASE WHEN FreqDet <= DAY(@date) THEN CAST(YEAR(DATEADD(MONTH,1,@DATE)) AS VARCHAR)+'-'+CAST(MONTH(DATEADD(MONTH,1,@DATE)) AS VARCHAR)+'-'+FreqDet
    	      ELSE CAST(YEAR(@DATE) AS VARCHAR)+'-'+CAST(MONTH(@DATE) AS VARCHAR)+'-'+FreqDet
    	END
    END
      FROM @table

    Monday, February 23, 2015 3:57 PM
  • @Patrick,

    Thank you very much!

    Best,

    Gabriel


    Gabriel A. Cánepa

    Tuesday, February 24, 2015 2:37 PM