Format Date Time Function

,

The Script is used to get  datetime in comminly used formats, it also contains some basic date time features.

If anyone wants to add more options , they are more than likely to do so, please just mention my name in the script.

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 
create  FUNCTION [dbo].[KDT_FN_FORMATDATETIME](
	@DateFormat			varchar(50), 
	@Date				DATETIME 
)
/*
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

Rate

4.5 (2)

Share

Share

Rate

4.5 (2)