Convert date to YYYYMM format

Question

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

User · Answer

I know it is an old topic  but If your SQL server version is higher than 2012   There is another simple option can choose  FORMAT function   SELECT FORMAT GetDate    yyyyMM     sqlfiddle

User · Answer

You can convert your date in many formats  for example    CONVERT NVARCHAR 10   DATE OF DAY  103    gt  15 09 2016 CONVERT NVARCHAR 10   DATE OF DAY  3    gt  15 09 16   Syntaxe    CONVERT  TheTypeYouWant    TheDateToConvert    TheCodeForFormating         The code is an integer  here 3 is the third formating without century  if you want the century just change the code to 103    In your case  i ve just converted and restrict size by nvarchar 6  like this    CONVERT NVARCHAR 6   DATE OF DAY  112    gt  201609   See more at   http   www w3schools com sql func convert asp

User · Answer

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

User · Answer

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

User · Answer

SELECT LEFT CONVERT varchar  GetDate   112  6

User · Answer

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

User · Answer

SELECT CONVERT nvarchar 6   GETDATE    112

[sql] Convert date to YYYYMM format

Examples related to sql

Examples related to sql-server

Examples related to tsql