create function [dbo].[FormatDate]
(
@dDate datetime --Date value to be formatted
,@sFormat varchar(40) --Format for date value
)
returns varchar(40)
as
begin
-- Insert the Month
-- ~~~~~~~~~~~~~~~~
set @sFormat = replace(@sFormat,'MMMM',datename(month,@dDate))
set @sFormat = replace(@sFormat,'MMM',convert(char(3),datename(month,@dDate)))
set @sFormat = replace(@sFormat,'MM',right(convert(char(4),@dDate,12),2))
set @sFormat = replace(@sFormat,'M1',convert(varchar(2),convert(int,right(convert(char(4),@dDate,12),2))))
-- Insert the Day
-- ~~~~~~~~~~~~~~
set @sFormat = replace(@sFormat,'DDDD',datename(weekday,@dDate))
set @sFormat = replace(@sFormat,'DDD',convert(char(3),datename(weekday,@dDate)))
set @sFormat = replace(@sFormat,'DD',right(convert(char(6),@dDate,12),2))
set @sFormat = replace(@sFormat,'D1',convert(varchar(2),convert(int,right(convert(char(6),@dDate,12),2))))
-- Insert the Year
-- ~~~~~~~~~~~~~~~
set @sFormat = replace(@sFormat,'YYYY',convert(char(4),@dDate,112))
set @sFormat = replace(@sFormat,'YY',convert(char(2),@dDate,12))
-- Return the function's value
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~
return @sFormat
end
(
@dDate datetime --Date value to be formatted
,@sFormat varchar(40) --Format for date value
)
returns varchar(40)
as
begin
-- Insert the Month
-- ~~~~~~~~~~~~~~~~
set @sFormat = replace(@sFormat,'MMMM',datename(month,@dDate))
set @sFormat = replace(@sFormat,'MMM',convert(char(3),datename(month,@dDate)))
set @sFormat = replace(@sFormat,'MM',right(convert(char(4),@dDate,12),2))
set @sFormat = replace(@sFormat,'M1',convert(varchar(2),convert(int,right(convert(char(4),@dDate,12),2))))
-- Insert the Day
-- ~~~~~~~~~~~~~~
set @sFormat = replace(@sFormat,'DDDD',datename(weekday,@dDate))
set @sFormat = replace(@sFormat,'DDD',convert(char(3),datename(weekday,@dDate)))
set @sFormat = replace(@sFormat,'DD',right(convert(char(6),@dDate,12),2))
set @sFormat = replace(@sFormat,'D1',convert(varchar(2),convert(int,right(convert(char(6),@dDate,12),2))))
-- Insert the Year
-- ~~~~~~~~~~~~~~~
set @sFormat = replace(@sFormat,'YYYY',convert(char(4),@dDate,112))
set @sFormat = replace(@sFormat,'YY',convert(char(2),@dDate,12))
-- Return the function's value
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~
return @sFormat
end