通过使用case语句组合两个VarChar字段来为当前月份的SQL设置变量 [英] SQL Setting Variable for Current Year Month by Combining two VarChar fields using case statement
问题描述
我试图基于称为YEARMO的字段声明当前年份和月份的变量.
I am trying to declare a variable for the current year and month based on a field called YEARMO.
这是一个6个字符的varchar字段,前4个字符代表年份,后2个字符代表月份(例如,2018年2月为201802).
It's a 6 character varchar field with the first 4 characters representing the year and the next 2 characters representing the month (ex. February 2018 as 201802).
如果只有一个数字的月份(十月之前的任何月份),我需要在月份变量前加0.当前代码返回"20182",并希望它返回"201802".
I need the month variable to have a 0 in front of it if is a month that only has one digit (any month before October). Current code returns '20182' and would like it to return '201802'.
declare @current_month varchar(6)
set @current_month = CAST( year(getdate()) as varchar(4))
+ case when len(CAST( month(getdate()) as varchar(2))) < 1 then '0' +
CAST( month(getdate()) as varchar(2))
else CAST( month(getdate()) as varchar(2))
end
select @current_month;
推荐答案
您的代码过于复杂.使用right
代替:
Your code is over complicated. Use right
instead:
declare @current_month char(6)
set @current_month = CAST( year(getdate()) as varchar(4)) + right('00' + cast(month(getdate()) as varchar(2)), 2)
select @current_month;
结果:
201802
如果您确实要使用case
,则应将1
更改为2
:
If you really want to use case
instead, you should change the 1
to 2
:
set @current_month = CAST( year(getdate()) as varchar(4)) +
case when len(CAST(month(getdate()) as varchar(2))) < 2 then
'0' + CAST( month(getdate()) as varchar(2)) else
CAST( month(getdate()) as varchar(2)) end
这篇关于通过使用case语句组合两个VarChar字段来为当前月份的SQL设置变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!