In sql server, Convert function is used to show the date and time values in particular formats. SQL Server's build in function to format datetime value is not good enough as it need to remember the number of each format. This is the generic function to format with user friendly format string such as dd/MM/yyyy hh:mm:ss tt

Code Snippet

CREATE FUNCTION [dbo].[ufn_FormatDateTime]    
 @InputDate DATETIME,    
 @Format VARCHAR(50)    
SET @Format = REPLACE(@Format,'AM','TT')     
SET @Format = REPLACE(@Format,'PM','TT')    
SET @Format = REPLACE(@Format,'MI','II')    
SET @Format = REPLACE(@Format,'DDDD','XX')    
SET @Format = REPLACE(@Format,'DDD','X')    
SET @Format = REPLACE(@Format,'DD', CASE WHEN DAY(@InputDate) < 10 THEN '0' + CAST(DAY(@InputDate) AS VARCHAR(2)) ELSE CAST(DAY(@InputDate) AS VARCHAR(2)) END)    
SET @Format = REPLACE(@Format,'D',CAST(DAY(@InputDate) AS VARCHAR(2)))    
SET @Format = REPLACE(@Format,'MMMM','XXXX')    
SET @Format = CASE WHEN @Format LIKE('%MON%') THEN REPLACE(@Format,'MMM','FFF') ELSE @Format END 
SET @Format = REPLACE(@Format,'MON',SUBSTRING(DATENAME(MM,@InputDate),1,3))  
SET @Format = CASE  
     WHEN @Format LIKE('%FFF%') THEN REPLACE(@Format,'MMM',SUBSTRING(DATENAME(MM,@InputDate),1,3))  
     WHEN @Format COLLATE SQL_Latin1_General_CP1_CS_AS LIKE('%mmm%') THEN REPLACE(@Format,'mmm' COLLATE SQL_Latin1_General_CP1_CS_AS,'FFF')  
SET @Format = REPLACE(@Format,'MM' COLLATE SQL_Latin1_General_CP1_CS_AS,CASE WHEN MONTH(@InputDate) < 10 THEN '0' + CAST(MONTH(@InputDate) AS VARCHAR(2)) 
              ELSE CAST(MONTH(@InputDate) AS VARCHAR(2)) END)    
SET @Format = REPLACE(@Format,'mm' COLLATE SQL_Latin1_General_CP1_CS_AS,'II')  
SET @Format = REPLACE(@Format,'M',CAST(MONTH(@InputDate) AS VARCHAR(2)))    
SET @Format = REPLACE(@Format,'YYYY',CAST(YEAR(@InputDate) AS VARCHAR(4)))    
SET @Format = REPLACE(@Format,'YY',SUBSTRING(CAST(YEAR(@InputDate) AS VARCHAR(4)),3,4))    
--Time Part ( AM / PM / 24 Hours )    
 DECLARE @tmpInt AS INT   
 DECLARE @tmpStr AS VARCHAR(5)     
 SET @tmpInt = DATEPART(hh,@InputDate)    
 SET @tmpStr = CAST(@tmpInt AS VARCHAR(2))    
IF(@Format LIKE('%HH%') AND @Format LIKE('%TT%'))     
 SET @tmpStr = CASE WHEN @tmpInt=0 THEN 12 WHEN @tmpInt > 12 THEN CAST(@tmpInt - 12 AS VARCHAR(2)) ELSE @tmpStr END   
 SET @tmpStr = REPLICATE('0',2-LEN(@tmpStr)) + @tmpStr    
 SET @Format = REPLACE(@Format,'HH',@tmpStr)    
ELSE IF (@Format LIKE('%H%') AND @Format LIKE('%TT%'))     
 SET @tmpStr = CASE WHEN @tmpInt=0 THEN 12 WHEN @tmpInt > 12 THEN CAST(@tmpInt - 12 AS VARCHAR(2)) ELSE @tmpStr END   
 SET @Format = REPLACE(@Format,'H',@tmpStr)    
ELSE IF (@Format LIKE('%HH%'))    
 SET @tmpStr = REPLICATE('0',2-LEN(@tmpStr)) + @tmpStr    
 SET @Format = REPLACE(@Format,'HH',@tmpStr)    
ELSE IF (@Format LIKE('%H%'))    
 SET @Format = REPLACE(@Format,'H',@tmpStr)    
SET @tmpInt = DATEPART(MI,@InputDate)  
SET @tmpStr = CAST(@tmpInt AS VARCHAR(2))  
SET @tmpStr = REPLICATE('0',2-LEN(@tmpStr)) + @tmpStr    
SET @Format = REPLACE(@Format,'II',@tmpStr)   
SET @tmpInt = DATEPART(ss,@InputDate)  
SET @tmpStr = CAST(@tmpInt AS VARCHAR(2))  
SET @tmpStr = REPLICATE('0',2-LEN(@tmpStr)) + @tmpStr    
SET @Format = REPLACE(@Format,'SS',@tmpStr)    
SET @Format = REPLACE(@Format,'TT',CASE WHEN DATEPART(hh,@InputDate) >= 12 THEN 'PM' ELSE 'AM' END)    
IF(@Format LIKE('%FFF%'))    
 SET @tmpInt = DATEPART(ms,@InputDate)    
 SET @tmpStr = CAST(@tmpInt AS VARCHAR(3))    
 SET @Format = REPLACE(@Format,'FFF', REPLICATE('0',3-LEN(@tmpStr)) + @tmpStr)    
SET @Format = REPLACE(@Format,'XXXX',DATENAME(MM,@InputDate))      
SET @Format = REPLACE(@Format,'XX',DATENAME(DW,@InputDate))      
SET @Format = REPLACE(@Format,'X',SUBSTRING(DATENAME(DW,@InputDate),1,3))    
RETURN @Format    

Sample Usage

SELECT dbo.ufn_FormatDateTime(GETDATE(),'YYYY-MM-DD HH:mm:SS tt')
--Output : 2012-02-05 01:58:38 AM

SELECT dbo.ufn_FormatDateTime(GETDATE(),'(dddd) mmmm dd, yyyy hh:mm:ss.fff tt')
--Output : (Sunday) February 05, 2012 01:58:38.723 AM

SELECT dbo.ufn_FormatDateTime(GETDATE(),'dd/MM/yyyy')
--Output : 05/02/2012

SELECT dbo.ufn_FormatDateTime(GETDATE(),'yyyy MMM, dd (ddd) hh:mm:ss tt')
-- Output : 2012 Feb, 05 (Sun) 01:58:38 AM


It follow the .NET style format string pattern.

YYYY4 digit year2012Any Case
YY2 digit year12Any Case
MMMMMonth's full nameFebruaryUpper Case
MON3 char month nameFebAny Case
MMM3 char month nameFebUpper Case
MM2 digit month leading with zero if required02Upper Case
Mmonth with no leading zero2Any Case
DDDDDay's full nameSundayAny Case
DDD3 char day nameSunAny Case
DD2 digit day with leading zero if required01Any Case
Dday with no leading zero1Any Case
HHHour with leading zero if required01Any Case
mmMinute01Lower Case
MIMinute01Any Case
SSSecond01Any Case
TTTime portion ( AM/PM )AMAny Case
FFFMiliSecond111Any Case
mmmMiliSecond111Lower Case

If I miss something else, please let me know.

author's photo

Author : Blue Cloud

Just a normal software developer who love to write code. Developing professional software since 1999. After working in the software industry for many years, I've started my own website to share knowledges and experiences.

blog comments powered by Disqus