Get the year and Qtr

Question

Get the year and Qtr

Papil

SSCommitted

Points: 1983
More actions
September 22, 2017 at 9:49 am

#339354

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.

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

The reason for the conversion error is the "2017-02" is not a date, it contains no day. Dates consist of 3 parts; Year, Month and day. If you omit one of those parts, SQL Server can't convert it.

You'll need to make the value a date, if you want want to treat them as such. For example:
WITH Dates AS ( SELECT * FROM (VALUES ('2017-01'),('2017-02'),('2017-03'),('2017-04'),('2017-05'),('2017-06'),('2017-07')) D(M)) ,FormattedDates AS ( SELECT CONVERT(date,LEFT(M,4) + RIGHT(M,2) + '01') AS MonthStart FROM Dates) SELECT MonthStart, CONVERT(varchar(4),DATEPART(YEAR, MonthStart)) + ' Q' + CONVERT(char,DATEPART(QUARTER, MonthStart)) AS MonthQuarter FROM FormattedDates;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jason A. Long SSC-Insane Points: 23781 More actions · Answer 3

Here's another option...
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL DROP TABLE #TestData;

CREATE TABLE #TestData ( OrigDateString CHAR(7) NOT NULL ); INSERT #TestData (OrigDateString) VALUES ('2017-02'),('2017-03'),('2017-04'),('2017-05'),('2017-06');

SELECT YearQ = CONCAT(LEFT(td.OrigDateString, 4), ' Q', DATEPART(QUARTER, od.OrigDate)) FROM #TestData td CROSS APPLY ( VALUES (DATEFROMPARTS(LEFT(td.OrigDateString, 4), RIGHT(td.OrigDateString, 2), 1)) ) od (OrigDate);

Results...
YearQ ------------------ 2017 Q1 2017 Q1 2017 Q2 2017 Q2 2017 Q2

Papil SSCommitted Points: 1983 More actions · Answer 4

Jason A. Long - Friday, September 22, 2017 10:36 AM
Here's another option...
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL DROP TABLE #TestData; CREATE TABLE #TestData ( OrigDateString CHAR(7) NOT NULL ); INSERT #TestData (OrigDateString) VALUES ('2017-02'),('2017-03'),('2017-04'),('2017-05'),('2017-06');
SELECT YearQ = CONCAT(LEFT(td.OrigDateString, 4), ' Q', DATEPART(QUARTER, od.OrigDate)) FROM #TestData td CROSS APPLY ( VALUES (DATEFROMPARTS(LEFT(td.OrigDateString, 4), RIGHT(td.OrigDateString, 2), 1)) ) od (OrigDate);
Results...
YearQ ------------------ 2017 Q1 2017 Q1 2017 Q2 2017 Q2 2017 Q2

Thanks

ScottPletcher SSC Guru Points: 100952 More actions · Answer 5

Or just:

SELECT LEFT(OrigDateString, 4) + ' Q' + CAST((CAST(SUBSTRING(OrigDateString, 6, 2) AS tinyint) + 2) / 3 AS varchar(1)) FROM #TestData

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 6

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)

Jason A. Long SSC-Insane Points: 23781 More actions · Answer 7

Even simpler still...

SELECT YearQ = CONCAT(LEFT(td.OrigDateString, 4), ' Q', DATEPART(QUARTER, od.OrigDate)) FROM #TestData td CROSS APPLY ( VALUES (CAST(td.OrigDateString + '-01' AS DATE)) ) od (OrigDate);

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 8

When I looked at the posted solutions, I was pretty sure that Scott's would be the fastest because of both not using CROSS APPLY and the Integer math he used. I was right.

We can simplify the code a bit by using Thom's original to quickly isolate the pieces, use implicit conversions done auto-magically for Scott's code plus that of the CONCAT function that Jason used to erg out another 9-13% performance improvement. Here's a million row test jig and the code (uses a throw-away variable in each case to take disk and display out of the picture). Wonderful things happen when a group of people get together on a problem

--===== Create and populate a million row test table IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL DROP TABLE #TestTable ; SELECT TOP 1000000 [YYYY-MM] = CONVERT(CHAR(7),RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'1900','2100')+DATEADD(dd,0,'1900'),120) INTO #TestTable FROM sys.all_columns ac1, sys.all_columns ac2 ; CHECKPOINT ; --===== Wait several seconds for things to "cool off" WAITFOR DELAY '00:00:05' ; GO PRINT REPLICATE('=',120); PRINT '-- Thom''s method redacted for the common #TestTable'; PRINT REPLICATE('=',120); DECLARE @Bitbucket CHAR(7); SET STATISTICS TIME ON; WITH FormattedDates AS (SELECT CONVERT(DATE,LEFT([YYYY-MM],4) + RIGHT([YYYY-MM],2) + '01') AS MonthStart FROM #TestTable) SELECT @Bitbucket = CONVERT(VARCHAR(4),DATEPART(YEAR, MonthStart)) + ' Q' + CONVERT(CHAR,DATEPART(QUARTER, MonthStart)) FROM FormattedDates; SET STATISTICS TIME OFF; GO PRINT REPLICATE('=',120); PRINT '-- Scott''s method redacted for the common #TestTable'; PRINT REPLICATE('=',120); DECLARE @Bitbucket CHAR(7); SET STATISTICS TIME ON; SELECT @Bitbucket = LEFT([YYYY-MM], 4) + ' Q' + CAST((CAST(SUBSTRING([YYYY-MM], 6, 2) AS tinyint) + 2) / 3 AS varchar(1)) FROM #TestTable; SET STATISTICS TIME OFF; GO PRINT REPLICATE('=',120); PRINT '-- Jason''s 2nd method redacted for the common #TestTable'; PRINT REPLICATE('=',120); DECLARE @Bitbucket CHAR(7); SET STATISTICS TIME ON; SELECT @Bitbucket = CONCAT(LEFT(td.[YYYY-MM], 4), ' Q', DATEPART(QUARTER, od.OrigDate)) FROM #TestTable td CROSS APPLY ( VALUES (CAST(td.[YYYY-MM] + '-01' AS DATE)) ) od (OrigDate); SET STATISTICS TIME OFF; GO PRINT REPLICATE('=',120); PRINT '-- Jeff''s method'; PRINT REPLICATE('=',120); DECLARE @Bitbucket CHAR(7); SET STATISTICS TIME ON; SELECT @Bitbucket = CONCAT(LEFT(td.[YYYY-MM],4),' Q',(RIGHT([YYYY-MM],2)+2)/3) FROM #TestTable td SET STATISTICS TIME OFF; GO

Here's are the results from the run I did.

(1000000 rows affected) ======================================================================================================================== -- Thom's method redacted for the common #TestTable ======================================================================================================================== SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 1500 ms, elapsed time = 1511 ms.
========================================================================================================================
--      Scott's method redacted for the common #TestTable
========================================================================================================================
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 500 ms, elapsed time = 493 ms.
========================================================================================================================
--      Jason's 2nd method redacted for the common #TestTable
========================================================================================================================
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 750 ms, elapsed time = 738 ms.
========================================================================================================================
--      Jeff's method
========================================================================================================================
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 447 ms.

--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)

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 9

Ah... almost forgot. Neither Scott's nor mine checks to make sure the values of YYYY and MM are actually part of a valid date. Ostensibly, that's already been checked but if the data is from a 3rd party source, then use Jason's. Thom's code also does the date check but Jason's is faster.

--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)

Jason A. Long SSC-Insane Points: 23781 More actions · Answer 10

Good test Jeff. Thank you for putting that together. Just for the fun of it, I plugged my 1st solution into your harness and it turns out the 1st method is is ~90 ms faster than the second. It's still not as fast as your's or Scott's, but still an unexpected improvement.
I hadn't considered the possibility of an invalid YYYY-MM value either, so rather than allowing it to error out on a single bad value, I replaced the CAST with TRY_CAST in the 2nd solution. No discernible difference difference in the TRY_CAST & CAST in terms of speed but something to consider if invalid values is a concern.
I never fail to be amazed by the the things that can be accomplished using integer math... +1000 to you and Scott for using it in your solutions!

(1000000 rows affected) ======================================================================================================================== -- Thom's method redacted for the common #TestTable ======================================================================================================================== SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 797 ms, elapsed time = 799 ms. ======================================================================================================================== -- Scott's method redacted for the common #TestTable ======================================================================================================================== SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 235 ms, elapsed time = 239 ms. ======================================================================================================================== -- Jason's 1st (DATEFROMPARTS) method redacted for the common #TestTable ======================================================================================================================== SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 359 ms, elapsed time = 366 ms. ======================================================================================================================== -- Jason's 2nd (CAST) method redacted for the common #TestTable ======================================================================================================================== SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 437 ms, elapsed time = 445 ms. ======================================================================================================================== -- Jason's 3rd (TRY_CAST) method redacted for the common #TestTable ======================================================================================================================== SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times: CPU time = 453 ms, elapsed time = 449 ms. ======================================================================================================================== -- Jeff's method ======================================================================================================================== SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 234 ms, elapsed time = 244 ms.

ScottPletcher SSC Guru Points: 100952 More actions · Answer 11

Jeff Moden - Saturday, September 23, 2017 8:24 PM
Ah... almost forgot. Neither Scott's nor mine checks to make sure the values of YYYY and MM are actually part of a valid date. Ostensibly, that's already been checked but if the data is from a 3rd party source, then use Jason's. Thom's code also does the date check but Jason's is faster.

True for date validity. However, my code will fail only if it is non-numeric, it doesn't have to be a valid date/month value.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 12

ScottPletcher - Monday, September 25, 2017 8:14 AM
Jeff Moden - Saturday, September 23, 2017 8:24 PM
Ah... almost forgot. Neither Scott's nor mine checks to make sure the values of YYYY and MM are actually part of a valid date. Ostensibly, that's already been checked but if the data is from a 3rd party source, then use Jason's. Thom's code also does the date check but Jason's is faster.
True for date validity. However, my code will fail only if it is non-numeric, it doesn't have to be a valid date/month value.

Yep... that's what I'm talking about. Neither of our solutions make sure it's a valid value.

--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)

ScottPletcher SSC Guru Points: 100952 More actions · Answer 13

Jeff Moden - Monday, September 25, 2017 9:40 AM
ScottPletcher - Monday, September 25, 2017 8:14 AM
Jeff Moden - Saturday, September 23, 2017 8:24 PM
Ah... almost forgot. Neither Scott's nor mine checks to make sure the values of YYYY and MM are actually part of a valid date. Ostensibly, that's already been checked but if the data is from a 3rd party source, then use Jason's. Thom's code also does the date check but Jason's is faster.
True for date validity. However, my code will fail only if it is non-numeric, it doesn't have to be a valid date/month value.
Yep... that's what I'm talking about. Neither of our solutions make sure it's a valid value.

I suppose it's a matter of philosophy too. In my view, queries should not check the validity of data. Instead, at INSERT time, a trigger or other follow-on process should do that. Otherwise you have to write data checking into every query that uses the data -- assuming you knew about it at the time! -- which is just not logical really.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jason A. Long SSC-Insane Points: 23781 More actions · Answer 14

ScottPletcher - Monday, September 25, 2017 9:53 AM
Jeff Moden - Monday, September 25, 2017 9:40 AM
ScottPletcher - Monday, September 25, 2017 8:14 AM
Jeff Moden - Saturday, September 23, 2017 8:24 PM
Ah... almost forgot. Neither Scott's nor mine checks to make sure the values of YYYY and MM are actually part of a valid date. Ostensibly, that's already been checked but if the data is from a 3rd party source, then use Jason's. Thom's code also does the date check but Jason's is faster.
True for date validity. However, my code will fail only if it is non-numeric, it doesn't have to be a valid date/month value.
Yep... that's what I'm talking about. Neither of our solutions make sure it's a valid value.
I suppose it's a matter of philosophy too. In my view, queries should not check the validity of data. Instead, at INSERT time, a trigger or other follow-on process should do that. Otherwise you have to write data checking into every query that uses the data -- assuming you knew about it at the time! -- which is just not logical really.

I've gotta go w/ Scott of this one... At some point, you have to trust that your data is what it's supposed to be... Then again, I'd never store a date as a varchar to begin with (If I only cared about year and month, I'd just set the values to 1st of the month)... So there's that...

In any case, I think it's safe to definitively say that the integer math method is substantially faster than the datepart(qq, cast(col as date)) method.