How do I execute scripts based on what month/day it is?

Question

Okay, I feel like I should be able to do this, but I have searched to no avail.

I need to show one of two different data sets depending on what day of the year it is. Specifically, if today is October 31 (or earlier,) I want to pull all entries from October 1 of last year to the end of current year. If today is after October 31, I want my data set to show entries from October 1 of the current year to the end of next year.

The code below is creating a simple "calendar" table variable that is storing all the months that exist in a specific dataset. Right now I am just limiting it to dates for current year. I would like to replace that with code to allow it to function as I stated above. I thought of using an IF statement, but I cannot figure out how to compare with getdate() day and month only.

DECLARE @calendar TABLE 
(       mon_name    VARCHAR(10)
    ,   mon_number  INT
    ,   yr          INT
)

INSERT INTO @calendar
SELECT      DATENAME(m,departure_date)
        ,   MONTH(departure_date)
        ,   YEAR(departure_date)
FROM        trip_mission
WHERE       departure_date          <>'1/1/1900' 
AND         YEAR(departure_date)    = YEAR(getdate())
GROUP BY    DATENAME(m,departure_date)
        ,   MONTH(departure_date)
        ,   YEAR(departure_date)

A simplified form of the question may be how can I run some code only if today is <= October 31. It would be super easy if I could say <= October 31, 2012, but I need it to be dynamic so that it will flip over every year.

October 2-31 meets both the requirements specified. I think you need to edit your question. Did you mean `If today is after November 1` ? — RThomas, May 01 '12 at 23:15
Oops, thanks for catching the typo. Edited post. October 31 is the date to "switch" the data being pulled. The actual data sets do overlap some intentionally, but only one of the two is to be used, depending on what side of October 31 today is. Make sense? Hard to explain when I only have 1 data set showing now. — gmaness, May 02 '12 at 00:00
RThomas - On second glance I realized what you were talking about. I have updated the post to theorize the same date range for a dataset. I need it to flip to the next "view" every October 31. — gmaness, May 02 '12 at 00:12
@gmaness - Just curious, must it be all inline? It is probably possible, but assigning a @start/@end range (based on the current month) seems cleaner. Not to mention 'WHERE dateColumn >= @start and DateColumn < @end` is often more "index friendly" than using a bunch of functions. — Leigh, May 02 '12 at 01:05

score 1 · Answer 1 · 2012-05-02T00:56:39.293

The query filters only records of current year and then uses CASE expression to assign value of 1 if departure date falls in November and December and 0 for all other months. The same logic is used to assign for today's date. Both the CASE expression values are then compared to fetch the matching records.

If you want to switch to a different month, say from 31, October to 31, March, then the value 10 in CASE expression has to be changed to 03.

Click here to view the demo in SQL Fiddle.

Script:

CREATE TABLE trip_mission 
(
    departure_date  DATETIME NOT NULL
);

INSERT INTO trip_mission (departure_date) VALUES
    ('2012-04-30'),
    ('2011-01-01'),
    ('2012-01-30'),
    ('2012-04-30'),
    ('2013-01-01'),
    ('2012-11-01'),
    ('2012-12-01'),
    ('2012-11-12'),
    ('1900-01-01'),
    ('2012-10-31');

SELECT  departure_date
FROM    trip_mission tm
WHERE   YEAR(departure_date)    = YEAR(GETDATE()) 
AND     (CASE WHEN MONTH(departure_date) > 10 THEN 1 ELSE 0 END) =
        (CASE WHEN MONTH(GETDATE())      > 10 THEN 1 ELSE 0 END)

Output:

DEPARTURE_DATE
-----------------------
2012-04-30 00:00:00.000
2012-01-30 00:00:00.000
2012-04-30 00:00:00.000
2012-10-31 00:00:00.000

score 1 · Accepted Answer · answered May 02 '12 at 11:29

The following little trick will allow you to select different subsets based on what month it is:

WHERE departure_date >= DATEADD(
  YEAR,
  MONTH(GETDATE()) / 11 - 1,
  CAST(YEAR(GETDATE()) AS char(4)) + '1001'
)

The last argument forms the date of the 1st of October of the current year. The second argument controls whether we subtract 1 year or not. Assuming the current year to be 2012, here's what we get depending on the current month

Current month  MONTH(GETDATE())  MONTH(…) / 11  MONTH(…) / 11 - 1  DATEADD(…)
-------------  ----------------  -------------  -----------------  ----------
January        1                 0              -1                 2011-10-01
February       2                 0              -1                 2011-10-01
March          3                 0              -1                 2011-10-01
April          4                 0              -1                 2011-10-01
May            5                 0              -1                 2011-10-01
June           6                 0              -1                 2011-10-01
July           7                 0              -1                 2011-10-01
August         8                 0              -1                 2011-10-01
September      9                 0              -1                 2011-10-01
October        10                0              -1                 2011-10-01
November       11                1              0                  2012-10-01
December       12                1              0                  2012-10-01

Nice one! I was thinking something similar. But mine needed an extra variable :) — Leigh, May 02 '12 at 15:07
Andriy, nicely done! I will still have to "massage" it quite a bit to fit my overall application, but I went ahead and marked this as the answer as I think it is the path I need to follow. Thanks! — gmaness, May 02 '12 at 20:09

How do I execute scripts based on what month/day it is?

2 Answers2