Devise a mask for the date format you desire and pass this in with the date e.g.
SELECT [dbo].[DateFormattedByMask] (GETDATE(),'DDDD DD MMM YYYY hh:mi')
could produce
Monday 30 Sep 2013 11:05
Limitations include no option for single digit day and month numbers e.g 24 hours after the previous example gives
Tuesday 01 Oct 2013 11:05
which may be desirable in some circumstances but not all.
/*
YYYY - Year, 4 digit
YY - Year, 2 digit
MMMM - Month name
MMM - Month name, first 3 characters
MM - Month, 2 digit
DDDD - Day of week
DDD - Day of week, first 3 characters
DD - Day of month, 2 digit
hh - hour (24 hour clock, 2 digit)
mi - minutes, 2 digit
ss - seconds, 2 digit
*/CREATE FUNCTION [dbo].[DateFormattedByMask]
(
@InputDate DATETIME,
@Mask NVARCHAR(40)
)
RETURNS VARCHAR(40)
AS
BEGIN
SELECT @Mask = REPLACE(@Mask,'YYYY',DATENAME(YEAR,@InputDate))
SELECT @Mask = REPLACE(@Mask,'YY',RIGHT(DATENAME(YEAR,@InputDate),2))
SELECT @Mask = REPLACE(@Mask,'MMMM', DATENAME(MONTH,@InputDate))
SELECT @Mask = REPLACE(@Mask,'MMM', LEFT(DATENAME(MONTH,@InputDate),3))
SELECT @Mask = REPLACE(@Mask,'MM', RIGHT('0'+CAST(MONTH(@InputDate) AS NVARCHAR(2)),2))
SELECT @Mask = REPLACE(@Mask,'DDDD', DATENAME(dw,@InputDate))
SELECT @Mask = REPLACE(@Mask,'DDD', LEFT(DATENAME(dw,@InputDate),3))
SELECT @Mask = REPLACE(@Mask,'DD', RIGHT('0'+DATENAME(DAY,@InputDate),2))
SELECT @Mask = REPLACE(@Mask,'hh', RIGHT('0'+DATENAME(hh,@InputDate),2))
SELECT @Mask = REPLACE(@Mask,'mi', RIGHT('0'+DATENAME(mi,@InputDate),2))
SELECT @Mask = REPLACE(@Mask,'ss', RIGHT('0'+DATENAME(ss,@InputDate),2))
RETURN @Mask
END
GO 
2014-11-25
1,907 reads
Function do return common datatime formats of a date.
select dbo.[KDT_FN_FORMATDATETIME]('LASTDAY DDDD',getdate())
2016-04-26 (first published: 2016-03-29)
1,616 reads
Read a short comparison of datetime and datetime2 types. Learn about their structure, as well as the advantages and disadvantages of each.
2019-09-19 (first published: 2016-01-05)
28,285 reads
Find minimum and maximum values for all date columns in a table or range of tables, using a mask.
2016-09-15 (first published: 2014-10-27)
2,991 reads
This stored procedure will show all possible datetime formats when used with the style argument in the CONVERT() function. It also shows sample output from the date part arguments.
2016-04-13 (first published: 2014-10-10)
2,490 reads