Use DATENAME and wrap the logic in a Function, not a Stored Proc
declare @myTime as DateTime
set @myTime = GETDATE()
select @myTime
select DATENAME(day, @myTime) + SUBSTRING(UPPER(DATENAME(month, @myTime)), 0,4)
Returns "14OCT"
Try not to use any Character / String based operations if possible when working with dates. They are numerical (a float) and performance will suffer from those data type conversions.
Dig these handy conversions I have compiled over the years...
/* Common date functions */
--//This contains common date functions for MSSQL server
--//some portions from Greg Larsen @ http://www.databasejournal.com/features/mssql/article.php/3076421
--//the rest from my own testing.
/*Getting Parts of a DateTime*/
--//gets the date only, 20x faster than using Convert/Cast to varchar
--//this has been especially useful for JOINS
SELECT (CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime))
--//gets the time only (date portion is '1900-01-01' and is considered the "0 time" of dates in MSSQL, even with the datatype min value of 01/01/1753.
SELECT (GETDATE() - (CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime)))
/*Calculating Work Days*/
--//this will calculate the number of week days that elapse between two dates. Holidays are NOT considered.
--//from Jeff Moden @ http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/
DECLARE @StartDate DateTime, @EndDate DateTime
SET @StartDate = '01/01/2007'
SET @EndDate = '12/31/2007 23:59:59:997'
SELECT (
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
) as 'WorkingDays'
/*Relative Dates*/
--//These are all functions that will calculate a date relative to the current date and time
/*Current Day*/
--//now
SELECT (GETDATE())
--//midnight of today
SELECT (DATEADD(ms,-4,(DATEADD(dd,DATEDIFF(dd,0,GETDATE()) + 1,0))))
--//Current Hour
SELECT DATEADD(hh,DATEPART(hh,GETDATE()),CAST(FLOOR(CAST(GETDATE() AS FLOAT)) as DateTime))
--//Current Half-Hour - if its 9:36, this will show 9:30
SELECT DATEADD(mi,((DATEDIFF(mi,(CAST(FLOOR(CAST(GETDATE() as FLOAT)) as DateTime)), GETDATE())) / 30) * 30,(CAST(FLOOR(CAST(GETDATE() as FLOAT)) as DateTime)))
/*Yearly*/
--//first datetime of the current year
SELECT (DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))
--//last datetime of the current year
SELECT (DATEADD(ms,-4,(DATEADD(yy,DATEDIFF(yy,0,GETDATE()) + 1,0))))
/*Monthly*/
--//first datetime of current month
SELECT (DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))
--//last datetime of the current month
SELECT (DATEADD(ms,-4,DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))))
--//first datetime of the previous month
SELECT (DATEADD(mm,DATEDIFF(mm,0,GETDATE()) -1,0))
--//last datetime of the previous month
SELECT (DATEADD(ms, -4,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))
/*Weekly*/
--//previous monday at 12AM
SELECT (DATEADD(wk,DATEDIFF(wk,0,GETDATE()) -1 ,0))
--//previous friday at 11:59:59 PM
SELECT (DATEADD(ms,-4,DATEADD(dd,5,DATEADD(wk,DATEDIFF(wk,0,GETDATE()) -1 ,0))))
/*Quarterly*/
--//first datetime of current quarter
SELECT (DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0))
--//last datetime of current quarter
SELECT (DATEADD(ms,-4,DATEADD(qq,DATEDIFF(qq,0,GETDATE()) + 1,0)))
/*Detecting leap year*/
CREATE FUNCTION dbo.IsLeapYear(@Year int)
RETURNS bit
AS
BEGIN
DECLARE @RetVal bit
IF (@Year % 400) = 0 --//every 400 years
SET @RetVal = 1
ELSE
BEGIN
IF (@Year % 100) = 0
SET @RetVal = 0
ELSE
BEGIN
IF (@Year % 4) = 0
SET @RetVal = 1
ELSE
SET @RetVal = 0
END
END
RETURN @RetVal
END
GO