I want to select value = 201301
select getdate(), cast(datepart(year, getdate()) as varchar(4))+cast(datepart(MONTH, getdate()) as varchar(2))
it returns 20131
what is the normal way to do this?
This question is related to
sql
sql-server
tsql
A more efficient method, that uses integer math rather than strings/varchars, that will result in an int type rather than a string type is:
SELECT YYYYMM = (YEAR(GETDATE()) * 100) + MONTH(GETDATE())
Adds two zeros to the right side of the year and then adds the month to the added two zeros.
SELECT CONVERT(nvarchar(6), GETDATE(), 112)
Actually, this is the proper way to get what you want, unless you can use MS SQL 2014 (which finally enables custom format strings for date times).
To get yyyymm
instead of yyyym
, you can use this little trick:
select
right('0000' + cast(datepart(year, getdate()) as varchar(4)), 4)
+ right('00' + cast(datepart(month, getdate()) as varchar(2)), 2)
It's faster and more reliable than gettings parts of convert(..., 112)
.
It's month 1, so you're getting an expected value. you'll have to zeropad the month (1 -> 01), as per this answer: How do I convert an int to a zero padded string in T-SQL?
You can convert your date in many formats, for example :
CONVERT(NVARCHAR(10), DATE_OF_DAY, 103) => 15/09/2016
CONVERT(NVARCHAR(10), DATE_OF_DAY, 3) => 15/09/16
Syntaxe :
CONVERT('TheTypeYouWant', 'TheDateToConvert', 'TheCodeForFormating' * )
In your case, i've just converted and restrict size by nvarchar(6) like this :
CONVERT(NVARCHAR(6), DATE_OF_DAY, 112) => 201609
See more at : http://www.w3schools.com/sql/func_convert.asp
Source: Stackoverflow.com