formatDate_scalarUDF.sql #blog

formatDate_scalarUDF.sql

-- http://spaghettidba.com/2012/03/23/sql-server-and-custom-date-formats/
/*
 * Returns a data formatted according to the format String.
 * The format string can contain the following tokens in any order:
 *
 * yy	--> Year, two digits
 * YYYY	--> Year, four digits
 * MM	--> Month, two digits
 * m	--> Month, one digit
 * DD	--> Day, two digits
 * d	--> Day, one digit
 * HH	--> Hour, two digits
 * h	--> Hour, one digit
 * NN	--> Minute, two digits
 * n	--> Minute, one digit
 * SS	--> Second, two digits
 * s	--> Second, one digit
 * AP	--> AM/PM
 * 
 * Any character not in the token list gets concatenated
 * to the string and left untouched.
 *
 * EXAMPLE: 
 * SELECT dbo.formatDate(GETDATE(), 'YYYY-MM-DD hh:nn:ss')
 * OUTPUT: 2007-01-25 17:35:21
 *
 * SELECT dbo.formatDate(GETDATE(), 'DD-MM-YYYY')
 * OUTPUT: 25-01-2007
 */
CREATE FUNCTION [dbo].[formatDate](@date as datetime, @format_string as varchar(50)  )
RETURNS varchar(50) 
AS  
BEGIN 
	DECLARE @format varchar(50)
	DECLARE @result AS varchar(50)
	DECLARE @iter AS int
	DECLARE @prevchar AS char(1) 
	DECLARE @currchar AS char(1) 
	DECLARE @currtoken AS varchar(4)
	

	SET @iter = 1
	SET @result = ''
	SET @format = CONVERT(varchar(50),@format_string) COLLATE Latin1_General_CS_AS

	WHILE @iter <= LEN(@format)
	BEGIN
		SET @currchar = CONVERT(char(1),SUBSTRING(@format,@iter,1)) COLLATE Latin1_General_CS_AS
		IF @currchar <> @prevchar OR @iter = LEN(@format)
		BEGIN
			SET @currtoken = 
				CASE (@prevchar) COLLATE Latin1_General_CS_AS
					WHEN 'Y' THEN RIGHT('0000' + CAST(YEAR(@date) AS varchar(4)),4)
					WHEN 'y' THEN RIGHT('00' + CAST(YEAR(@date) AS varchar(4)),2)
					WHEN 'M' THEN RIGHT('00' + CAST(MONTH(@date) AS varchar(2)),2)
					WHEN 'm' THEN CAST(MONTH(@date) AS varchar(2))
					WHEN 'D' THEN RIGHT('00' + CAST(DAY(@date) AS varchar(2)),2)
					WHEN 'd' THEN CAST(DAY(@date) AS varchar(2))
					WHEN 'H' THEN RIGHT('00' + CAST(DATEPART(hour,@date) AS varchar(2)),2)
					WHEN 'h' THEN CAST(DATEPART(hour,@date) AS varchar(2))
					WHEN 'N' THEN RIGHT('00' + CAST(DATEPART(minute,@date) AS varchar(2)),2)
					WHEN 'n' THEN CAST(DATEPART(minute,@date) AS varchar(2))
					WHEN 'S' THEN RIGHT('00' + CAST(DATEPART(second,@date) AS varchar(2)),2)
					WHEN 's' THEN CAST(DATEPART(second,@date) AS varchar(2))
					WHEN 'A' THEN CASE WHEN DATEPART(hour,@date) >= 12 THEN 'PM' ELSE 'AM' END
					WHEN ' ' THEN ' '
					ELSE RTRIM(@prevchar)
				END
			SET @result = @result + @currtoken
		END
		SET @prevchar = @currchar COLLATE Latin1_General_CS_AS
		SET @iter = @iter + 1
	END
	RETURN @result
END