Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Convert date to quarter + year

Posted on 2007-10-18
Medium Priority
3,533 Views
Need to convert a date to quarters.  for instance, i retrieve a date field from a database using SQL and it is currently in this format: "10/25/2007 8:00:00 AM".  I need to have the value of this field converted into a specific quarter name based on the month.  So, since the date is october, I need it to say "Fall - 2007".

So here is the logic:  If the date month is January, February or March (display "Winter - Year")
If the date month is April, May or June (display "Spring - Year")
If the date month is July, August, September (display "Summer - Year")
and of course October, November, December (display "Fall - Year")

Help is greatly appreciated!!!!
0
Question by:fusionfx2000
• 4
• 3
• 3
• +1

LVL 18

Expert Comment

ID: 20102351
Display = CASE WHEN MONTH(YourDate) BETWEEN 1 AND 3 THEN 'Winter - ' + CAST(YEAR(YourDate) AS CHAR(4)
WHEN MONTH(YourDate) BETWEEN 4 AND 6 THEN 'Spring - ' + CAST(YEAR(YourDate) AS CHAR(4)
WHEN MONTH(YourDate) BETWEEN 7 AND 9 THEN 'Summer - ' + CAST(YEAR(YourDate) AS CHAR(4)
WHEN MONTH(YourDate) BETWEEN 10 AND 12 THEN 'Fall - ' + CAST(YEAR(YourDate) AS CHAR(4)
END
0

LVL 17

Expert Comment

ID: 20102383
Case When DatePart(mm, myDate) In ('1','2','3') Then 'Winter - ' + Cast(DatePart(yy, myDate) as varchar(4)
When DatePart(mm, myDate) In ('4', '5', '6') Then 'Spring - ' + Cast(DatePart(yy, myDate) as varchar(4)
When DatePart(mm, myDate) In ('7', '8', '9') Then 'Summer - ' + Cast(DatePart(yy, myDate) as varchar(4)
When DatePart(mm, myDate) In ('10', '11', '12') Then 'Fall - ' + Cast(DatePart(yy, myDate) as varchar(4)
Else 'Date Doesn''t Resolve'
End
0

Author Comment

ID: 20103576
Unified,

I get the following error:
"Error in list of function arguments: 'IN' not recognized.
Error in list of function arguments: ',' not recognized.
Error in list of function arguments: ')' not recognized.
Unable to parse query text."

This is what the case part looks like:
CASE TAFD WHEN DatePart(mm, tuv.TaskActualFinishDate) IN ('1', '2', '3') THEN 'Winter - ' + Cast(DatePart(yy, tuv.TaskActualFinishDate) AS varchar(4)
WHEN DatePart(mm, tuv.TaskActualFinishDate) IN ('4', '5', '6') THEN 'Spring - ' + Cast(DatePart(yy, tuv.TaskActualFinishDate) AS varchar(4)
WHEN DatePart(mm, tuv.TaskActualFinishDate) IN ('7', '8', '9') THEN 'Summer - ' + Cast(DatePart(yy, tuv.TaskActualFinishDate) AS varchar(4)
WHEN DatePart(mm, tuv.TaskActualFinishDate) IN ('10', '11', '12') THEN 'Fall - ' + Cast(DatePart(yy, tuv.TaskActualFinishDate) AS varchar(4)
ELSE 'Date Doesnt Resolve ' END
0

Author Comment

ID: 20103583
sorry....previous is for cmangus
0

Author Comment

ID: 20103652
Unified,

I get the following when running your script:

incorrect syntax near when.

Here is the script as I have it:

Display = CASE WHEN MONTH(tuv.TaskActualFinishDate) BETWEEN 1 AND 3 THEN 'Winter - ' + CAST(YEAR(tuv.TaskActualFinishDate) AS CHAR(4)
WHEN MONTH(tuv.TaskActualFinishDate) BETWEEN 4 AND 6 THEN 'Spring - ' + CAST(YEAR(tuv.TaskActualFinishDate) AS CHAR(4)
WHEN MONTH(tuv.TaskActualFinishDate) BETWEEN 7 AND 9 THEN 'Summer - ' + CAST(YEAR(tuv.TaskActualFinishDate) AS CHAR(4)
WHEN MONTH(tuv.TaskActualFinishDate) BETWEEN 10 AND 12 THEN 'Fall - ' + CAST(YEAR(tuv.TaskActualFinishDate) AS CHAR(4)
END
0

LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 20103847
SELECT CASE DATEPART(QUARTER, datefield)
WHEN 1 THEN 'Winter'
WHEN 2 THEN 'Spring'
WHEN 3 THEN 'Summer'
ELSE 'Fall' END + ' - ' + CAST(YEAR(datefield) AS CHAR(4)) AS [Season-Yr]
FROM ...
0

LVL 18

Expert Comment

ID: 20104553
Good call ScottPletcher, I wasn't aware of the quarter datepart.
0

LVL 17

Expert Comment

ID: 20104579
Yep, good stuff from ScottPletcher, as usual...

I would change one thing though.  I would add:

WHEN 4 THEN 'FALL'
Else 'Unresolved Data'
End + ' - ' + CAST(YEAR(datefield) AS CHAR(4)) AS [Season-Yr]

...so you can catch any bad or missing dates more appropriately.
0

LVL 70

Expert Comment

ID: 20104942
DATEPART(QUARTER,...) should return *only* 1-4 or NULL -- no other values are possible.

And if it's NULL, you will get NULL back for the full expression, because YEAR(NULL) will be NULL :-).
0

LVL 17

Expert Comment

ID: 20104969
Agreed.  But does the user want NULL back for the full expression?  Wouldn't it be a bit gentler to return something that indicated we had an issue with the underlying date and couldn't determine the quarter?
0

LVL 70

Expert Comment

ID: 20104997
But you'll still get NULL back from the full expression no matter what comes out of the first expression, since the second expression is NULL.

To return a msg in place of NULL, we need to do this:

SELECT CASE WHEN datefield IS NULL THEN '(no date found)'  --<<-- chg msg as needed
ELSE
CASE DATEPART(QUARTER, datefield)
WHEN 1 THEN 'Winter'
WHEN 2 THEN 'Spring'
WHEN 3 THEN 'Summer'
ELSE 'Fall' END + ' - ' + CAST(YEAR(datefield) AS CHAR(4)) AS [Season-Yr]
END
FROM ...
0

LVL 17

Expert Comment

ID: 20105012
I get ya now...Thanks!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error â€œConnecting to the Integration Services service on the computer failed with the following error: 'The specified service â€¦
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then readingâ€¦
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlinâ€¦
###### Suggested Courses
Course of the Month21 days, 7 hours left to enroll