CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Shahab Haidar
Shahab Haidar

Reputation: 641

Get only month and year in SQL Server

I want to fetch only month and year from a date column in SQL Server.

Example: if today's date is 02/03/2019, then I want 0319.

Note: I want the result in same order (2 digit month and 2 digit year). Zero should not be removed from month.

Upvotes: 0

Views: 1740

Answers (4)

Hamed
Hamed

Reputation: 11

Use function DATEPART in TSQL to get any part of a DateTime value. e.g:

DATEPART(yy,datecol) gives you 4 digit year part of a DateTime column (e.g: datecol), using the % (modulus) operator you can get 2 digit year DATEPART(yy,datecol)%100. DATEPART(mm,datecol) gives you month part of the datecol field.

select Right('0'+cast(DATEPART(mm,datecol) as varchar(2)),2) +
       Right('0'+cast(DATEPART(yy,datecol)%100 as varchar(2)),2) MonthYearPart
from MyTable

Regards

Upvotes: 0

Thom A
Thom A

Reputation: 96027

As an alternative approach, you could go for:

RIGHT(REPLACE(CONVERT(varchar(8),DateColumn,3),'/',''),4)

Upvotes: 1

SNR
SNR

Reputation: 772

You can try this

substring(convert(nvarchar,@date,12),3,2) + left(convert(nvarchar,@date,12),2)

You can create an user defined function, and then apply to your column/s

create function udf_Getmonthyear(@date as date)
RETURNS nchar(4)
BEGIN
    DECLARE @d_format nchar(6) = (select convert(nvarchar,@date,12))

    RETURN (select SUBSTRING(@d_format,3,2) + left(@d_format,2)) 
end
go

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can create a number using:

select month(datecol) * 100 + (year(datecol) % 100)

Prepending the zeros requires a bit more work:

select right('0' + convert(varchar(255), month(datecol) * 100 + (year(datecol) % 100)), 4)

Or, you can use format():

select format(datecol, 'MMyy')

Upvotes: 1

Related Questions