The SQL Server helper function to format datetime values.
create function dbo.FormatDateTime(
@DateFormat varchar(50),
@Date datetime
)
/*------------------------------------------------------------------------------
Converts different datetime formats and relavant date parts.
usage
select dbo.FormatDateTime('YYYY-MM-DD',getdate());
select dbo.FormatDateTime('LASTDAY DDDD',getdate());
select dbo.FormatDateTime('Dayofyear', GETDATE()+200);
select dbo.FormatDateTime('LASTDAY DDDD', GETDATE()+245);
select dbo.FormatDateTime('YYYY-MM-DD', GETDATE()+245);
Parameters | Result
________________________________________________________________________
dd-mmm-yyyy | 22-OCT-2008
MMM DD YYYY HH-mm Am/pm | Oct 22 2008 6-02AM
MM/DD/YYYY | 10/22/2008
YYYY.MM.DD | 2008.10.22
DD/MM/YYYY | 22/10/2008
DD.MM.YYYY | 22.10.2008
DD/MM/YYYY | 22/10.2008
DD-MM-YYYY | 22-10-2008
DD MMM YYYY | 22 Oct 2008
MMM DD,YYYY | Oct 22, 2008
HH-mm-SS | 06-33-22
MMM DD YYYY H-mm-SS-MSAM/PM | Oct 22 2008 6-12-30-540AM
DD-MM-YYYY | 22-10-2008
YYYY/MM/DD | 2008/10/22
YYYYMMDD | 20081022
DD MMM YYYY HH-mm-SS-MS | Oct 22 2008 6-12-30-540AM (will actually by colon, but we can use here since install cd breaks)
HH-mm-SS-MS | 06-18-30-740
Julion | 39741
Day | 22
Month | 10
Year | 2008
YYYY-MM | 2008-10
MM-YYYY | 10-2008
YYYY MM | 2008 10
MM YYYY | 10 2008
Week | 43 week in year
Dayofyear | 293 day in year
quarter | 4 Year Quarter
Weekday | 4 day number of week
MMMM | October
DDDD | Wednesday
DDDD DD MMMM YYYY | Wednesday 22 October 2008
DDDD MMMM YYYY | Wednesday October 2008
LASTDAY | 2008/10/31 (last day of month)
LASTDAY DDDD | Friday (last day of month)
YYYY-MM-DD | 2008-11-11
-----------------------------------------------------------------------------*/
returns varchar(30)
as
begin
declare @myRes varchar(25);
if @DateFormat = 'dd-mmm-yyyy'
begin
set @myRes = Cast(day(@Date) as varchar(2))+'-'+substring(datename(m, @Date), 1, 3)+'-'+cast(year(@Date) as varchar(4));
end;
-- 'Returns the date MMM DD YYYY HH:mm Am/pm'
-- 'EG Oct 22 2008 6:02AM'
if @DateFormat = 'MMM DD YYYY HH:mm Am/pm'
begin
set @myRes = CONVERT(varchar(30), @Date, 100);
end;
-- Returns the date MM/DD/YYYY
-- EG 10/22/2008
if @DateFormat = 'MM/DD/YYYY'
begin
set @myRes = CONVERT(varchar(30), @Date, 101);
end;
-- Returns the date YYYY.MM.DD
-- EG 2008.10.22
if @DateFormat = 'YYYY.MM.DD'
begin
set @myRes = CONVERT(varchar(30), @Date, 102);
end;
-- Returns the date DD/MM/YYYY
-- EG 22/10/2008
if @DateFormat = 'DD/MM/YYYY'
begin
set @myRes = CONVERT(varchar(30), @Date, 103);
end;
-- Returns the date DD.MM.YYYY
-- EG 22.10.2008
if @DateFormat = 'DD.MM.YYYY'
begin
set @myRes = CONVERT(varchar(30), @Date, 104);
end;
-- Returns the date DD-MM-YYYY
-- EG 22-10-2008
if @DateFormat = 'DD-MM-YYYY'
begin
set @myRes = CONVERT(varchar(30), @Date, 105);
end;
-- Returns the date DD MMM YYYY
-- EG 22 Oct 2008
if @DateFormat = 'DD MMM YYYY'
begin
set @myRes = CONVERT(varchar(30), @Date, 106);
end;
-- Returns the date DD MMM YYYY
-- EG Oct 22, 2008
if @DateFormat = 'MMM DD,YYYY'
begin
set @myRes = CONVERT(varchar(30), @Date, 107);
end;
-- 'Returns the time HH:mm:SS'
-- 'EG 06:11:24'
if @DateFormat = 'HH:mm:SS'
begin
set @myRes = CONVERT(varchar(30), @Date, 108);
end;
-- 'Returns the Date MMM DD YYYY H:mm:SS:MSAM/PM'
-- 'EG Oct 22 2008 6:12:30:540AM'
if @DateFormat = 'MMM DD YYYY H:mm:SS:MSAM/PM'
begin
set @myRes = CONVERT(varchar(30), @Date, 109);
end;
-- Returns the Date DD-MM-YYYY
-- EG 10-22-2008
if @DateFormat = 'DD-MM-YYYY'
begin
set @myRes = CONVERT(varchar(30), @Date, 110);
end;
-- Returns the Date YYYY/MM/DD
-- EG 2008/10/22
if @DateFormat = 'YYYY/MM/DD'
begin
set @myRes = CONVERT(varchar(30), @Date, 111);
end;
-- Returns the Date YYYYMMDD
-- EG 20081022
if @DateFormat = 'YYYYMMDD'
begin
set @myRes = CONVERT(varchar(30), @Date, 112);
end;
-- 'Returns the Date DD MMM YYYY HH:mm:SS:MS'
-- 'EG 22 Oct 2008 06:17:18:883'
if @DateFormat = 'DD MMM YYYY HH:mm:SS:MS'
begin
set @myRes = CONVERT(varchar(30), @Date, 113);
end;
-- 'Returns the time HH:mm:SS:MS'
-- 'EG 06:18:30:740'
if @DateFormat = 'HH:mm:SS:MS'
begin
set @myRes = CONVERT(varchar(30), @Date, 114);
end;
-- Returns the Date Julion
-- EG 2008-10-22 is represented as 39741
if @DateFormat = 'Julion'
begin
set @myRes = CONVERT(varchar(30), DATEDIFF(day, CONVERT(datetime, '1900-01-01', 110), @Date));
end;
-- Returns the Day
-- EG 2008-10-22 is represented as 22
if @DateFormat = 'Day'
begin
set @myRes = CONVERT(varchar(30), DAY(@Date));
end;
-- Returns the Month
-- EG 2008-10-22 is represented as 10
if @DateFormat = 'Month'
begin
set @myRes = CONVERT(varchar(30), Month(@Date));
end;
-- Returns the year
-- EG 2008-10-22 is represented as 2008
if @DateFormat = 'Year'
begin
set @myRes = CONVERT(varchar(30), Year(@Date));
end;
-- Returns the date in YYYY-MM
-- EG 2008-10-22 is represented as 2008-10
if @DateFormat = 'YYYY-MM'
begin
set @myRes = CONVERT(varchar(30), Year(@Date))+'-'+CONVERT(varchar(30), Month(@Date));
end;
-- Returns the date in MM-YYYY
-- EG 2008-10-22 is represented as 10-2008
if @DateFormat = 'MM-YYYY'
begin
set @myRes = CONVERT(varchar(30), Month(@Date))+'-'+CONVERT(varchar(30), Year(@Date));
end;
-- Returns the date in YYYY MM
-- EG 2008-10-22 is represented as 2008-10
if @DateFormat = 'YYYY MM'
begin
set @myRes = CONVERT(varchar(30), Year(@Date))+' '+CONVERT(varchar(30), Month(@Date));
end;
-- Returns the date in MM YYYY
-- EG 2008-10-22 is represented as 10 2008
if @DateFormat = 'MM YYYY'
begin
set @myRes = CONVERT(varchar(30), Month(@Date))+' '+CONVERT(varchar(30), Year(@Date));
end;
-- Returns the date in WeekNo
-- EG 2008-10-22 is represented as week 43
if @DateFormat = 'Week'
begin
set @myRes = CONVERT(varchar(30), DATEPART(wk, @Date));
end;
-- Returns the date in Dayofyear
-- EG 2008-10-22 is represented as 296
if @DateFormat = 'Dayofyear'
begin
set @myRes = CONVERT(varchar(30), DATEPART(dy, @Date));
end;
-- Returns the Quarter of the year
-- EG 2008-10-22 is represented as week 4
if @DateFormat = 'quarter'
begin
set @myRes = CONVERT(varchar(30), DATEPART(qq, @Date));
end;
-- Returns the day of the week starting on Sunday as day 1
-- EG 2008-10-22 is represented as day 4
if @DateFormat = 'Weekday'
begin
set @myRes = CONVERT(varchar(30), DATEPART(dw, @Date));
end;
-- Returns the day of the long Month name format
-- EG 2008-10-22 is represented as day OCtober
if @DateFormat = 'MMMM'
begin
set @myRes = CONVERT(varchar(30), DAteNAME(month, @Date));
end;
-- Returns the day of the long Month name format
-- EG 2008-10-22 is represented as Wednesday
if @DateFormat = 'DDDD'
begin
set @myRes = CONVERT(varchar(30), DAteNAME(weekday, @Date));
end;
-- Returns the day of the long Month name format
-- EG 2008-10-22 is represented as 22 OCtober 2008
if @DateFormat = 'DDDD DD MMMM YYYY'
begin
set @myRes = DATENAME(weekday, @Date)+' '+CONVERT(varchar(30), DAtEPART(DAY, @Date))+' '+CONVERT(varchar(30), DAteNAME(month, @Date))+' '+CONVERT(varchar(30), DAtEPART(YEAR, @Date));
end;
-- Returns the day of the long Month name format
-- EG 2008-10-22 is represented as 22 OCtober 2008
if @DateFormat = 'DD MMMM YYYY'
begin
set @myRes = CONVERT(varchar(30), DAtEPART(DAY, @Date))+' '+CONVERT(varchar(30), DAteNAME(month, @Date))+' '+CONVERT(varchar(30), DAtEPART(YEAR, @Date));
end;
-- Returns the day of the last Day of month
-- EG 2008-10-22 is represented as 2008/10/31
if @DateFormat = 'LASTDAY'
begin
set @myRes = CONVERT(varchar(30), dateadd(day, -1 * day(dateadd(month, 1, @Date)), dateadd(month, 1, @Date)), 111);
end;
-- Returns the day of the last Day of month
-- EG 2008-10-22 is represented as Friday
if @DateFormat = 'LASTDAY DDDD'
begin
set @myRes = CONVERT(varchar(30), DATENAME(weekday, dateadd(day, -1 * day(dateadd(month, 1, @Date)), dateadd(month, 1, @Date))), 111);
end;
if @DateFormat = 'YYYY-MM-DD'
begin
declare @DAY char(2),
@MONTH char(2);
select @DAY = DAY(@Date);
if LEN(@DAY) < 2
begin
select @DAY = '0'+@DAY
end;
select @MONTH = MONTH(@Date);
if LEN(@MONTH) < 2
begin
select @MONTH = '0'+@MONTH
end;
set @myRes = CONVERT(varchar(4), Year(@Date))+'-'+@MONTH+'-'+@DAY;
end;
-- Return Results --
return @myRes;
end;