Extract Month and year in T-SQL [duplicate]

1. SELECT CONVERT(VARCHAR(6), GETDATE(), 112) 2. SELECT CAST(YEAR(GETDATE()) * 100 + MONTH(GETDATE()) AS VARCHAR(6)) 

Try this:

SELECT DATEPART(yyyy, @date) * 100 + DATEPART(mm, @date) 

If you want to do the operation on the current time then replace @date with CURRENT_TIMESTAMP.

The result is integer though, you may convert it to varchar(6) using CAST() or CONVERT() method.

Try.. FIDDLE DEMO

declare @dt datetime='2015-01-01' select CAST(YEAR(@dt)as Varchar(6))+ Case When Len(Cast(MONTH(@dt) as varchar(6)))>1 then CAST(MONTH(@dt) as varchar(6)) Else '0'+Cast(MONTH(@dt) as varchar(6)) End 

EDIT:2

declare @dt date='2015-01-01' select CAST(REPLACE(@dt,'-','')as varchar(6)) 

This is the simplest answer

select getdate() , convert( varchar(6) , getdate() ,112) 

and if you want year as 2 digit

select getdate() , convert( varchar(12) , getdate() ,12) 

Whenever you want to convert date format from standard format to another format, always check this link.

http://www.w3schools.com/sql/func_convert.asp

https://msdn.microsoft.com/en-us/library/ms187928.aspx

select CONVERT(nvarchar(6), DATENAME(yyyy, getdate())) + SUBSTRING(CONVERT(nvarchar(6),getdate(), 112),5,2) 

DATENAME:

Returns a character string that represents the specified datepart of the specified date

ref:

https://msdn.microsoft.com/en-IN/library/ms174395.aspx

for all datetime conversion this will help

http://www.sqlusa.com/bestpractices/datetimeconversion/