SQL Server - SELECT CASE and between?

Asked By SF on 06-Mar-08 12:07 AM
Hi,

I have a table with a date field. I want to construct a view by showing "1st
Qtr", "2nd Qtr" etc... from my date field value. So I start a new view
include the following:

SELECT     Me_Date, CASE ME_DATE WHEN BETWEEN #1/1/2008# AND #30/ 3/2008#
THEN "1ST"  WHEN BETWEEN #1/ 4/2008# AND
#30/6/2008# THEN "2ND" END
FROM         dbo.tblMeetings


I got error message with the word between.

Could someone advice?

SF




Plamen Ratchev replied on 06-Mar-08 12:37 AM
A couple problems:

- the date format
- when using simple CASE you need to provide a simple expression in WHEN
- the double quotes around 1ST and 2ND

Also, you can solve this problem using a calendar table that defined
quarters:
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Here is corrected version:

SELECT me_date,
CASE WHEN me_date BETWEEN '20080101' AND '20080330'
THEN '1ST'
WHEN me_date BETWEEN '20080401' AND '20080630'
THEN '2ND'
END AS Qtr
FROM dbo.tblMeetings

You can use DATEPART to get the quarter from the date:

SELECT me_date,
CASE DATEPART(qq, me_date)
WHEN 1 THEN '1ST'
WHEN 2 THEN '2ND'
END AS Qtr
FROM dbo.tblMeetings

HTH,

Plamen Ratchev
http://www.SQLStudio.com
SF replied on 06-Mar-08 02:12 AM
Thank you very much for your prompt response. It works very well.

SF