CODEWITHSUNDEEP
sqlsql-serveruser-defined-functionsdate-formattingscalar
jacksonSD
jacksonSD

Reputation: 677

function to display dates as YYYY-MMM

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

Upvotes: 2

Views: 17419

Answers (2)

slavoo
slavoo

Reputation: 6086

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;

Upvotes: 7

gotqn
gotqn

Reputation: 43656

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.

Upvotes: 1

Related Questions