CODEWITHSUNDEEP
sqlsql-server
noob
noob

Reputation: 3811

Converting yyyymm date to yyyyqn SQL

I want to convert date from format yyyymm like 202001 (2020 jan), 202002 (feb 2020) etc. to yyyyqn format for eg.

@currdate (yyyymm) Expected format
-----------------------------------
202001              2020Q1
202005              2020Q2 
202012              2020Q4   and so on

This is the query I tried:

declare @currdate nvarchar(6)
set @currdate = (SELECT FORMAT(GETDATE(), 'yyyyMM'))

select 
    left(@currdate, 4) as year, 
    cast(year as char(4)) + 'Q' + (select right(@currdate, 2) as month)

Error:

Invalid column name 'year'

Upvotes: 1

Views: 810

Answers (3)

Martin Smith
Martin Smith

Reputation: 453142

I'd do it like this (adding 2 to any value between 1-12 and then applying integer division by 3 will give your desired result in the range 1-4)

declare @d char(6) = '202001'

SELECT CONCAT(LEFT(@d,4),'Q',(RIGHT(@d,2)+2)/3)

Upvotes: 3

ScaisEdge
ScaisEdge

Reputation: 133360

You can't use a column alias in select you must repeat the code 

declare @currdate nvarchar(6)
set @currdate =(SELECT FORMAT(GetDate(),'yyyyMM'))
select left(@currdate,4) as year, cast(left(@currdate,4) as char(4)) 
 + 'Q' + (select right(@currdate,2) as month)

Upvotes: 2

Nick
Nick

Reputation: 147156

You can use CONVERT to convert @currdate to a date if you concat 01 to it and then use format 112 (ISO). Then you can use DATEPART to extract the quarter and put that in a CONCAT with the left 4 characters and a Q:

declare @currdate nvarchar(6)
set @currdate = '202001'
select concat(left(@currdate, 4), 'Q', datepart(quarter, convert(date, concat(@currdate, '01'), 112)))
set @currdate = '202008'
select concat(left(@currdate, 4), 'Q', datepart(quarter, convert(date, concat(@currdate, '01'), 112)))

Alternatively you can just divide the month by 3, take the FLOOR and add 1:

select concat(left(@currdate, 4), 'Q', floor(right(@currdate, 2) / 3) + 1)

In both cases the output is:

2020Q1
2020Q3

Demo on dbfiddle

Upvotes: 2

Related Questions