Instantly share code, notes, and snippets.

Embed
What would you like to do?
formatDate_scalarUDF.sql #blog
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment