Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Convert date to quarter + year

Posted on 2007-10-18
12
Medium Priority
?
3,533 Views
Last Modified: 2013-11-27
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
Comment
Question by:fusionfx2000
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
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

by:Chris Mangus
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

by:fusionfx2000
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

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

Author Comment

by:fusionfx2000
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

by:
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

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

Expert Comment

by:Chris Mangus
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

by:Scott Pletcher
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

by:Chris Mangus
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

by:Scott Pletcher
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

by:Chris Mangus
ID: 20105012
I get ya now...Thanks!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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

804 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question