Home SQL Setting Variable for Current Year Month by Combining two VarChar fields using case statement
Reply: 3

SQL Setting Variable for Current Year Month by Combining two VarChar fields using case statement

ant man
1#
ant man Published in 2018-02-13 15:46:17Z

I am trying to declare a variable for the current year and month based on a field called YEARMO.

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

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;
Zohar Peled
2#
Zohar Peled Reply to 2018-02-13 15:49:51Z

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;

Result:

201802

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
uzi
3#
uzi Reply to 2018-02-13 15:50:52Z

You can use Convert function if you are looking for current month

select convert(char(6), getdate(), 112)
Striker
4#
Striker Reply to 2018-02-13 15:51:30Z

declare @current_month varchar(6)

set @current_month = CAST(year(getdate())*100 + month(getdate()) as varchar(6))

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.475268 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO