RE: Get the year and Qtr – SQLServerCentral

SSC Guru

Points: 1003867

September 22, 2017 at 9:26 pm

#1960610

Luis Cazares - Friday, September 22, 2017 10:15 AM
Papil - Friday, September 22, 2017 9:48 AM
Ia am trying to get the year and Qtr from my date column which has values like- YYYY-MM (2017-02). I want the Year and Qtr as -2017 Q1
I tried below- select CONVERT(datetime, '2017-02',104) as Col1
it gives me Conversion failed when converting date and/or time from character string. error.
select DATEPART(YEAR,'2017-02') [Year] - gives error too-Conversion failed when converting date and/or time from character string.
can some one pls help with this. thanks.
A date needs day, month and year. If one of those parts is missing, it's not possible to have a date. Add a day to be able to convert to a date/time data type before obtaining the year and quarter. Another option is to use string functions instead of date functions.

I know you know this ol' friend and, although not applicable for this particular problem of YYYY-MM but for anyone watching, you only need one date part if the date part is year or two if the monthly date part is either the month name or a proper abbreviation of the month name

SELECT CONVERT(DATETIME,'2017') ,CONVERT(DATETIME,'MAR2017') ,CONVERT(DATETIME,'MAR 2017') ,CONVERT(DATETIME,'MARCH 2017') ,CONVERT(DATETIME,'2017Mar') ,CONVERT(DATETIME,'2017 Mar') ,CONVERT(DATETIME,'2017 March') ;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)