Technical Article

Simple function to return month and year

,

I occasionally need to return a month and year but would like the month as a 3 letter abbreviation instead of a number.  This is useful for reports.

Usage:
Select mydb.dbo.udfmmm_yyyy(Getdate())

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udfmmm_yyyy]') 
and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udfmmm_yyyy]
GO

CREATE FUNCTION dbo.udfmmm_yyyy  (@dttm DATETIME)
RETURNS nvarchar(10)
AS
BEGIN
   Declare @udfmmm_yyyy nvarchar(10)
  ,@Month nvarchar(3) 
SELECT    @Month = 
      CASE 
         WHEN Month(@dttm) = 1  THEN 'JAN'
         WHEN Month(@dttm) = 2  THEN 'FEB'
         WHEN Month(@dttm) = 3  THEN 'MAR'
         WHEN Month(@dttm) = 4  THEN 'APR'
         WHEN Month(@dttm) = 5  THEN 'MAY'
         WHEN Month(@dttm) = 6  THEN 'JUN'
         WHEN Month(@dttm) = 7  THEN 'JUL'
         WHEN Month(@dttm) = 8  THEN 'AUG'
         WHEN Month(@dttm) = 9  THEN 'SEP'
         WHEN Month(@dttm) = 10 THEN 'OCT'
         WHEN Month(@dttm) = 11 THEN 'NOV'
         WHEN Month(@dttm) = 12 THEN 'DEC'
      END
 
   SELECT @udfmmm_yyyy = @Month + ' ' + CAST(Year(@dttm) AS Varchar(4))
   RETURN(@udfmmm_yyyy)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating