将yyyymm日期转换为yyyyqn SQL

我想将日期从格式yyyymm转换为yyyyqn,例如202001(2020年1月),202002年2月(2020年2月)等。

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

这是我尝试过的查询:

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)

错误:

无效的列名“ year”
评论
Clare
Clare

我会这样

declare @d char(6) = '202001'

SELECT CONCAT(LEFT(@d,4),'q',(RIGHT(@d,2)+2)/3)
点赞
评论
ksit
ksit

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)

在这两种情况下,输出均为:

2020Q1
2020Q3

Demo on dbfiddle

点赞
评论
御林军
御林军

您不能在选择中使用列别名,必须重复代码

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)
点赞
评论