Date range in CASE statement

Transact-SQL
1

trying to use a date range in a CASE statement to figure out fiscal month.

here's what I've tried with no luck

CASE shmast.fshipdate
when shmast.fshipdate between '10/29/2011' and '10/29/2012' then '1'
when shmast.fshipdate between '10/29/2013' and '10/29/2014' then '2'
when shmast.fshipdate between '10/29/2014' and '10/29/2015' then '3'
ELSE 'X'
END as FM,

Thanks!

2

First off. CASE is an expression not a statement. Second, you must have received an error message like:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'between'.

Since your syntax is invalid. When you have errors, you should post the error message.

Third, you are using a locale-specific format in your case statement. Instead, use the locale-agnostic format yyyymmdd.

Probably you want something like this:

SELECT CASE 
            WHEN shmast.fshipdate BETWEEN '20111029' AND '20121029'
                  THEN '1 '
            WHEN shmast.fshipdate BETWEEN '20131029' AND '20141029 '
                  THEN ' 2 '
            WHEN shmast.fshipdate BETWEEN '20141029' AND '20151029 '
                  THEN ' 3 '
            ELSE ' X '
            END AS FM
FROM shmast
3

There are a few issues with the expression you have written, both in syntax and in the logic.

Use the following:

SELECT  
	CASE 
		WHEN shmast.fshipdate >= '20111029'
			AND shmast.fshipdate < '20121029' THEN '1'
		WHEN shmast.fshipdate >= '20121029'
			AND shmast.fshipdate < '20131029' THEN '2'
		WHEN shmast.fshipdate >= '20131029'
			AND shmast.fshipdate < '20141029' THEN '3'
		WHEN shmast.fshipdate >= '20141029'
			AND shmast.fshipdate < '20151029' THEN '4'
		ELSE 'X'
	END AS FM

There are a few things I want to point out:

  1. When you use the BETWEEN construct, it is inclusive. So you probably don't want to include 10/29 of a given year, and 10/29 of the following year.
  2. Use the YYYYMMDD format for dates
  3. Was the period from 2012 to 2013 an omission, or deliberate? I assumed it was an omissions. If not strike that.

:sniped: :sniped: :sniped: :sniped:

4

Thank you both for your time and help!

Tested and worked great.