How to add check for January in SQL statement

Question

I'm creating a report using SQL to pull logged labor hours from our labor database for the previous month. I have it working great, but need to add logic to prevent it from breaking when it runs in January. I've tried adding If/Then statements and CASE logic, but I don't know if I'm just not doing it right, or if our system can't process it. Here's the snippet that pulls the date range:

SELECT
  ...
FROM
  ...
WHERE
  ...
   AND
YEAR(ENTERDATE) = YEAR(current date) AND MONTH(ENTERDATE) = (MONTH(current date)-1)

What you're asking is similar to http://stackoverflow.com/questions/1424999/get-the-records-of-last-month-in-sql-server — Steve Howard, Nov 05 '13 at 19:36

Shai · Accepted Answer · 2013-11-05T19:43:39.377

1

Just use AND as a barrier like this. In January, the second clause will be executed instead of the first one:

SELECT
  ...
FROM
  ...
WHERE
  ...
  AND
  (
    (
      (MONTH(current date) > 1) AND
      (YEAR(ENTERDATE) = YEAR(current date) AND MONTH(ENTERDATE) = (MONTH(current date)-1))
      -- this one gets used from Feb-Dec
    )
    OR
    (
      (MONTH(current date) = 1) AND
      (YEAR(ENTERDATE) = YEAR(current date) - 1 AND MONTH(ENTERDATE) = 12)
      -- alternatively, in Jan only this one gets used
    )
  )

edited Nov 05 '13 at 19:43

answered Nov 05 '13 at 19:38

Shai

7,159
3
19
22

This doesn't throw any errors and is working for this month. Is there an easy way to test this before January? – demonic240 Nov 05 '13 at 19:44
Yeah. Replace all 3 instances of `MONTH(current date)` with a hardcoded `1` – Shai Nov 05 '13 at 19:47

score 1 · Answer 2 · answered Nov 05 '13 at 19:48

If your report is always going to be for the previous month, then I think the simplest idea is to declare the year and month of the previous month and then reference those in the Where clause. For example:

Declare LastMo_Month Integer = MONTH(DATEADD(MONTH,-1,getdate()));
Declare LastMo_Year Integer = YEAR(DATEADD(MONTH,-1,getdate()));

Select ...

Where MONTH(EnterDate) = @LastMo_Month
    and YEAR(EnterDate) = @LastMo_Year

You could even take it a step further and allow the report to be created for any number of months ago:

Declare Delay Integer = -1;
Declare LastMo_Month Integer = MONTH(DATEADD(MONTH,@Delay,getdate()));
Declare LastMo_Year Integer = YEAR(DATEADD(MONTH,@Delay,getdate()));

Select ...

Where MONTH(EnterDate) = @LastMo_Month
    and YEAR(EnterDate) = @LastMo_Year

Hope this helps.

PS - This is my first answer on StackOverflow, so sorry if the formatting isn't right!

PlantTheIdea · Answer 3 · 2013-11-05T20:22:19.073

0

Try adding the previous month and year to your SELECT statement:

SELECT
    ...
    ,CASE MONTH(current date)
        WHEN 1 THEN 12
        ELSE MONTH(current date)-1
    END AS previous_month
    ,CASE MONTH(current date)
        WHEN 1 THEN YEAR(current date)-1
        ELSE YEAR(current date)
    END AS previous_year
FROM
    ...
WHERE
    ...
    AND YEAR(ENTERDATE) = previous_year
    AND MONTH(ENTERDATE) = previous_month

This should allow you to set the value before the WHERE comparison. This should be the most performant way to perform this procedure, as it avoids creating two entirely separate clauses or using OR.

edited Nov 05 '13 at 20:22

answered Nov 05 '13 at 19:36

PlantTheIdea

16,061
5
35
40

That only helps with the month, which is/should be working. The problem I need to fix, is that in its current state, the report in January 2014 will try and pull labor report for December of 2014 instead of 2013. – demonic240 Nov 05 '13 at 19:40
i know u already selected an answer, but while it will work fine it is definitely not the most efficient way to handle the query. i've updated to include year components, which should give you what you want in a much more performant way. – PlantTheIdea Nov 05 '13 at 20:23

score 0 · Answer 4 · answered Nov 05 '13 at 19:38

0

if(month(getdate()) = 1)
begin
        your jan logic
end
else
begin
        your logic
end

The above answer with the Case is ok, but running a CASE on a huge result set would be pretty costly

answered Nov 05 '13 at 19:38

MichaelEvanchik

1,748
1
13
23

getdate() is not a recognized function in our database =/ – demonic240 Nov 05 '13 at 19:42
GETDATE() is MySQL. Question is for SQL Server – Shai Nov 05 '13 at 19:44
getdate() works fine in SQL server not sure whats up – MichaelEvanchik Nov 05 '13 at 20:20

Doon · Answer 5 · 2013-11-05T19:50:12.997

Which Dialect of SQL are you speaking?

As opposed to doing it all with case statements, just use the built it date / time functions to subtract a month from the current date, which should handle crossing year boundaries.

TransACT

  WHERE
   YEAR(ENTERDATE) = year(dateadd(MONTH,-1, CURRENT_TIMESTAMP))
   AND MONTH(ENTERDATE) = month(dateadd(MONTH,-1, CURRENT_TIMESTAMP))

Mysql

 WHERE
   YEAR(ENTERDATE) = YEAR(date_sub(curdate(),INTERVAL 1 MONTH))
   AND MONTH(ENTERDATE) = MONTH(date_sub(curdate(),INTERVAL 1 MONTH) )

score 0 · Answer 6 · answered Nov 05 '13 at 19:44

0

WHERE
...
AND
DATEPART(yy,ENTERDATE) = DATEPART(yy,DATEADD(m,-1,ENTERDATE))
AND DATEPART(m,ENTERDATE) = DATEPART(m,DATEADD(m,-1,ENTERDATE))

answered Nov 05 '13 at 19:44

Shiva

20,575
14
82
112

How to add check for January in SQL statement

6 Answers6