Format Date Time Function

create  FUNCTION [dbo].[KDT_FN_FORMATDATETIME](
@DateFormatvarchar(50), 
@DateDATETIME 
)
/*
Auther- Kraai (JF) Du Toit 
use :  select dbo.[KDT_FN_FORMATDATETIME]('YYYY-MM-DD',getdate())
       select dbo.[KDT_FN_FORMATDATETIME]('LASTDAY DDDD',getdate())
  
SELECT dbo.KDT_FN_FORMATDATETIME('Dayofyear', GETDATE()+200)
SELECT dbo.KDT_FN_FORMATDATETIME('LASTDAY DDDD', GETDATE()+245)
SELECT dbo.KDT_FN_FORMATDATETIME('YYYY-MM-DD', GETDATE()+245)
Purpose- Converting  To different datetime formats and relavant date parts 

Parameters|Result
________________________________________________________________________
dd-mmm-yyyy|   22-OCT-2008          
MMM DD YYYY HH-mm Am/pm|Oct 22 2008  6-02AM
MM/DD/YYYY|10/22/2008
YYYY.MM.DD|2008.10.22
DD/MM/YYYY|22/10/2008
DD.MM.YYYY|22.10.2008
DD/MM/YYYY|22/10.2008
DD-MM-YYYY|22-10-2008
DD MMM YYYY|22 Oct 2008
MMM DD,YYYY|   Oct 22, 2008
HH-mm-SS|06-33-22
MMM DD YYYY H-mm-SS-MSAM/PM|Oct 22 2008  6-12-30-540AM
DD-MM-YYYY|   22-10-2008
YYYY/MM/DD|2008/10/22
YYYYMMDD|20081022
DD MMM YYYY HH-mm-SS-MS|Oct 22 2008  6-12-30-540AM (will actually by colon, but we can use here since install cd breaks)
HH-mm-SS-MS|06-18-30-740
Julion|39741
Day|   22
Month|   10
Year|   2008
YYYY-MM|   2008-10
MM-YYYY|   10-2008
YYYY MM|2008 10
MM YYYY|   10 2008
Week|   43 week in year 
Dayofyear|   293 day in year 
quarter|   4 Year Quarter
Weekday|   4 day number of week
MMMM|October 
DDDD|   Wednesday
DDDD DD MMMM YYYY|   Wednesday 22 October 2008
DDDD MMMM YYYY|Wednesday  October 2008
LASTDAY|   2008/10/31 (last day of month)
LASTDAY DDDD|   Friday (last day of month)
YYYY-MM-DD|   2008-11-11 


*/RETURNS VARCHAR(30)
AS
BEGIN

DECLARE @myRes VARCHAR(25)


    IF @DateFormat = 'dd-mmm-yyyy'
    BEGIN
      SET @myRes =  Cast(day(@Date) as varchar(2))+'-'+(substring(datename(m, @Date),1,3))+'-'+cast(year(@Date) as varchar(4))
    END

-- 'Returns the date MMM DD YYYY HH:mm Am/pm'
-- 'EG Oct 22 2008  6:02AM'
IF @DateFormat = 'MMM DD YYYY HH:mm Am/pm' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),@Date,100)
END 


-- Returns the date MM/DD/YYYY 
-- EG 10/22/2008
IF @DateFormat = 'MM/DD/YYYY' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),@Date,101)
END 
-- Returns the date YYYY.MM.DD
-- EG 2008.10.22
IF @DateFormat = 'YYYY.MM.DD' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),@Date,102)
END 
-- Returns the date DD/MM/YYYY
-- EG 22/10/2008
IF @DateFormat = 'DD/MM/YYYY' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),@Date,103)
END 
-- Returns the date DD.MM.YYYY
-- EG 22.10.2008
IF @DateFormat = 'DD.MM.YYYY' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),@Date,104)
END 

-- Returns the date DD-MM-YYYY
-- EG 22-10-2008
IF @DateFormat = 'DD-MM-YYYY' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),@Date,105)
END 
-- Returns the date DD MMM YYYY
-- EG 22 Oct 2008
IF @DateFormat = 'DD MMM YYYY' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),@Date,106)
END 
-- Returns the date DD MMM YYYY
-- EG Oct 22, 2008
IF @DateFormat = 'MMM DD,YYYY' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),@Date,107)
END 

-- 'Returns the time HH:mm:SS'
-- 'EG  06:11:24'
IF @DateFormat = 'HH:mm:SS' 
BEGIN

SET @myRes = CONVERT(VARCHAR(30),@Date,108)
END 

-- 'Returns the Date MMM DD YYYY H:mm:SS:MSAM/PM'
-- 'EG  Oct 22 2008  6:12:30:540AM'
IF @DateFormat = 'MMM DD YYYY H:mm:SS:MSAM/PM' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),@Date,109)
END 

-- Returns the Date DD-MM-YYYY
-- EG  10-22-2008
IF @DateFormat = 'DD-MM-YYYY' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),@Date,110)
END 

-- Returns the Date YYYY/MM/DD
-- EG  2008/10/22
IF @DateFormat = 'YYYY/MM/DD' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),@Date,111)
END 

-- Returns the Date YYYYMMDD
-- EG  20081022
IF @DateFormat = 'YYYYMMDD' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),@Date,112)
END 

-- 'Returns the Date DD MMM YYYY HH:mm:SS:MS'
-- 'EG  22 Oct 2008 06:17:18:883'
IF @DateFormat = 'DD MMM YYYY HH:mm:SS:MS' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),@Date,113)
END 


-- 'Returns the time  HH:mm:SS:MS'
-- 'EG  06:18:30:740'
IF @DateFormat = 'HH:mm:SS:MS' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),@Date,114)
END 

-- Returns the Date Julion 
-- EG  2008-10-22 is represented as 39741
IF @DateFormat = 'Julion' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30), (DATEDIFF (day, CONVERT(datetime, '1900-01-01', 110)
                   , @Date)))
END 

-- Returns the Day
-- EG  2008-10-22 is represented as 22
IF @DateFormat = 'Day' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),DAY(@Date))
END 

-- Returns the Month
-- EG  2008-10-22 is represented as 10
IF @DateFormat = 'Month' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),Month(@Date))
END 

-- Returns the year
-- EG  2008-10-22 is represented as 2008
IF @DateFormat = 'Year' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),Year(@Date))
END 

-- Returns the date in YYYY-MM
-- EG  2008-10-22 is represented as 2008-10
IF @DateFormat = 'YYYY-MM' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),Year(@Date))+'-'+CONVERT(VARCHAR(30),Month(@Date))
END 

-- Returns the date in MM-YYYY
-- EG  2008-10-22 is represented as 10-2008
IF @DateFormat = 'MM-YYYY' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),Month(@Date))+'-'+CONVERT(VARCHAR(30),Year(@Date))
END 

-- Returns the date in YYYY MM
-- EG  2008-10-22 is represented as 2008-10
IF @DateFormat = 'YYYY MM' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),Year(@Date))+' '+CONVERT(VARCHAR(30),Month(@Date))
END 

-- Returns the date in MM YYYY
-- EG  2008-10-22 is represented as 10 2008
IF @DateFormat = 'MM YYYY' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),Month(@Date))+' '+CONVERT(VARCHAR(30),Year(@Date))
END 

-- Returns the date in WeekNo 
-- EG  2008-10-22 is represented as week 43
IF @DateFormat = 'Week' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),DATEPART(wk,@Date))
END 

-- Returns the date in Dayofyear 
-- EG  2008-10-22 is represented as 296
IF @DateFormat = 'Dayofyear' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),DATEPART(dy,@Date))
END 
-- Returns the Quarter of the year 
-- EG  2008-10-22 is represented as week 4
IF @DateFormat = 'quarter' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),DATEPART(qq,@Date))
END 

-- Returns the day of the week starting on Sunday as day 1
-- EG  2008-10-22 is represented as day 4 
IF @DateFormat = 'Weekday' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),DATEPART(dw,@Date))
END 

-- Returns the day of the long Month name format 
-- EG  2008-10-22 is represented as day OCtober 
IF @DateFormat = 'MMMM' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),DAteNAME(month,@Date ))
END 

-- Returns the day of the long Month name format 
-- EG  2008-10-22 is represented as  Wednesday
IF @DateFormat = 'DDDD' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),DAteNAME(weekday,@Date ))
END 


-- Returns the day of the long Month name format 
-- EG  2008-10-22 is represented as  22 OCtober  2008
IF @DateFormat = 'DDDD DD MMMM YYYY' 
BEGIN
SET @myRes = DATENAME(weekday,@Date)  + ' '+CONVERT(VARCHAR(30),DAtEPART(DAY,@Date ))+ ' '+ CONVERT(VARCHAR(30),DAteNAME(month, @Date)) + ' '+ CONVERT(VARCHAR(30),DAtEPART(YEAR,@Date ))
END 
-- Returns the day of the long Month name format 
-- EG  2008-10-22 is represented as 22 OCtober  2008
IF @DateFormat = 'DD MMMM YYYY' 
BEGIN
SET @myRes = CONVERT(VARCHAR(30),DAtEPART(DAY,@Date ))+ ' '+ CONVERT(VARCHAR(30),DAteNAME(month, @Date)) + ' '+ CONVERT(VARCHAR(30),DAtEPART(YEAR,@Date ))
END 

-- Returns the day of the last Day of month
-- EG  2008-10-22 is represented as 2008/10/31
IF @DateFormat = 'LASTDAY' 
BEGIN
SET @myRes =CONVERT(VARCHAR(30),dateadd(day,-1* day(dateadd(month,1,@Date)),dateadd(month,1,@Date)),111)
END 

-- Returns the day of the last Day of month
-- EG  2008-10-22 is represented as Friday
IF @DateFormat = 'LASTDAY DDDD' 
BEGIN
SET @myRes =CONVERT(VARCHAR(30), DATENAME(weekday,dateadd(day,-1* day(dateadd(month,1,@Date)),dateadd(month,1,@Date))), 111)

END

IF @DateFormat = 'YYYY-MM-DD' 
BEGIN
DECLARE  @DAY CHAR(2)
,@MONTH CHAR(2)
    
SELECT @DAY = DAY(@Date)
IF( LEN(@DAY) < 2)
 SELECT @DAY = '0'+@DAY
    
SELECT @MONTH = MONTH(@Date)
IF( LEN(@MONTH) < 2)
 SELECT @MONTH = '0'+@MONTH
SET @myRes = CONVERT(VARCHAR(4),Year(@Date))+'-'+@MONTH+'-'+@DAY
END

-- Return Results --
RETURN(@myRes);
END