CODEWITHSUNDEEP
sqlsql-server
cool breeze
cool breeze

Reputation: 4811

How to write a case statement with date ranges

I'm trying to return the Quarter based on the a datetime value.

I'm getting an error for the below script:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

DECLARE @d AS DATETIME
SET @d = GETDATE()

SELECT CASE 
        WHEN @d BETWEEN '2017-01-01' AND '2017-03-31' THEN 'Q1'
        WHEN @d BETWEEN '2017-04-01' AND '2017-06-31' THEN 'Q2'
        WHEN @d BETWEEN '2017-07-01' AND '2017-09-31' THEN 'Q3'
        WHEN @d BETWEEN '2017-10-01' AND '2018-12-31' THEN 'Q4'
        ELSE 'Q?'
        END

It seems to work fine with a single "WHEN" clause, but with 4 it gives an error.

What is the reason?

Upvotes: 1

Views: 9307

Answers (3)

Jonathan Applebaum
Jonathan Applebaum

Reputation: 5986

you have a built-in method for that:

SELECT DATEPART(QUARTER, @d)

Upvotes: 3

kicken
kicken

Reputation: 2167

The error is because June and September only have 30 days, but you're asking for their 31st day.

Also, your last when statement is a year off.

Upvotes: 1

Emilio Lucas Ceroleni
Emilio Lucas Ceroleni

Reputation: 1598

There is no such date as 2017-06-31. It should be 2017-06-30.

Upvotes: 0

Related Questions