SQL Server format date
Format Date in SQL Server by using a user defined functions.
/*
author: Rizwan Ansari
*/
create function dbo.fnFormatDate (@datetime datetime, @format varchar(35))
returns varchar(35)
as
begin
declare @date varchar(35)
set @date = @format
if (charindex ('yyyy',@date) > 0)
set @date = replace(@date, 'yyyy', datename(YY, @datetime))
else if (charindex ('yy',@date) > 0)
set @date = replace(@date, 'yy', right(datename(YY, @datetime),2))
else if (charindex ('y',@date) > 0)
set @date = replace(@date, 'y', right(datename(YY, @datetime),2))
if (charindex ('DD',@date) > 0)
set @date = replace(@date, 'DD', right('0'+datename(DD, @datetime),2))
else if (charindex ('D',@date) > 0)
set @date = replace(@date, 'D', datename(DD, @datetime))
if (charindex ('MMMM',@date) > 0)
set @date = replace(@date, 'MMMM', datename(MM, @datetime))
else if (charindex ('MMM',@date) > 0)
set @date = replace(@date, 'MMM', left(datename(MM, @datetime),3))
else if (charindex ('MM',@date) > 0)
set @date = replace(@date, 'MM', right('0'+convert(varchar,datepart(MM, @datetime)),2))
else if (charindex ('M',@date) > 0)
set @date = replace(@date, 'M', convert(varchar,datepart(MM, @datetime)))
return @date
end
go
Examples of using Format Date function
SELECT dbo.fnFormatDate (GETDATE(), 'MMMM DD, YYYY');
SELECT dbo.fnFormatDate (GETDATE(), 'YYYYMMDD');
SELECT dbo.fnFormatDate (GETDATE(), 'MM/DD/YYYY');
SELECT dbo.fnFormatDate (GETDATE(), 'M/D/YY');
SELECT dbo.fnFormatDate (GETDATE(), 'M/D/YYYY');
SELECT dbo.fnFormatDate (GETDATE(), 'YYYY-MM-DD');