function to display dates as YYYY-MMM
16,940
Solution 1
use DATENAME
DECLARE @InputDate datetime
SELECT @InputDate = '2013-10-10 10:10:10'
DECLARE @output nvarchar(8);
SELECT @output = CAST(DatePart(YYYY, @InputDate) as nvarchar(4)) + '-' + CONVERT(nvarchar(3), datename(month, @InputDate))
SELECT @output;
result:
2013-Oct
edit:
Create function fnYearMonth
(@InputDate date)
returns nvarchar(8)
Begin
Return CAST(DatePart(YYYY, @InputDate) as nvarchar(4)) + '-' + CONVERT(nvarchar(3), datename(month, @InputDate))
End;
Solution 2
Use DATENAME function:
Create function fnYearMonth
(@InputDate date)
returns VARCHAR(16)
Begin
Return CAST(YEAR(@InputDate) AS CHAR(4)) + ' '+ DATENAME ( month , @InputDate )
End;
Or
CAST(YEAR(@InputDate) AS CHAR(4)) + ' '+ LEFT(DATENAME ( MONTH , @InputDate ),3)
if you do not need the whole name of the month.
Author by
jacksonSD
Updated on August 26, 2022Comments
-
jacksonSD over 1 year
as the title states, I'm trying to make a little function to take date values already in the database and display them as YYYY-MMM (eg: 2013-Nov). SQL Server. I tried DatePart but it only takes one part arguement at a time (cant do YYYY-MMM). Any suggestions keeping it as a function?
Create function fnYearMonth (@InputDate date) returns date Begin Return DatePart(YYYY-MMM, @inputdate) End; Select hiredate, dbo.fnYearMonth(hiredate) as ReviewDate from employees Order by ReviewDate