Today i saw a thread in msdn asking about how to format datetime in his required format. The poster sited the ease of doing this in MySQL. It prompted me to write a simple datetime formatting function. Here it is.
IF Object_id('dbo.fn_format_date') IS NOT NULL
BEGIN
DROP FUNCTION dbo.fn_format_date
END
GO
CREATE FUNCTION dbo.Fn_format_date
(@datetime DATETIME,
@format VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @token TABLE(
id INT IDENTITY,
token VARCHAR(10),
VALUE VARCHAR(10)
)
INSERT INTO @token
(token,
VALUE)
-- Month in string
SELECT '%mth', Datename(MONTH,@datetime) UNION ALL -- Month full word
SELECT '%mon', Left(Datename(MONTH,@datetime),3) UNION ALL -- Month abrv
SELECT '%wday', Datename(weekday,@datetime) UNION ALL -- weekday
SELECT '%wd', Left(Datename(weekday,@datetime),3) UNION ALL -- weekday abrv
-- 4 digits year, 2 digits month, day etc
SELECT '%yyyy', Convert(VARCHAR(4),Datepart(YEAR,@datetime)) UNION ALL -- Year
SELECT '%mm', Right('00' + Convert(VARCHAR(2),Datepart(MONTH,@datetime)),
2) UNION ALL -- Month
SELECT '%dd', Right('00' + Convert(VARCHAR(2),Datepart(DAY,@datetime)),
2) UNION ALL -- Day
SELECT '%hh', Right('00' + Convert(VARCHAR(2),Datepart(HOUR,@datetime)),
2) UNION ALL -- Hour
SELECT '%mi', Right('00' + Convert(VARCHAR(2),Datepart(MINUTE,@datetime)),
2) UNION ALL -- Minute
SELECT '%ss', Right('00' + Convert(VARCHAR(2),Datepart(SECOND,@datetime)),
2) UNION ALL -- Second
SELECT '%ms', Right('000' + Convert(VARCHAR(2),Datepart(SECOND,@datetime)),
3) UNION ALL -- Millisecond
-- 2 digits year
SELECT '%yy', Right(Convert(VARCHAR(4),Datepart(YEAR,@datetime)),
2) UNION ALL -- Year
SELECT '%y', Right(Convert(VARCHAR(4),Datepart(YEAR,@datetime)),
1) UNION ALL -- Year
SELECT '%m', Convert(VARCHAR(2),Datepart(MONTH,@datetime)) UNION ALL
SELECT '%d', Convert(VARCHAR(2),Datepart(DAY,@datetime)) UNION ALL
SELECT '%h', Convert(VARCHAR(2),Datepart(HOUR,@datetime)) UNION ALL
SELECT '%i', Convert(VARCHAR(2),Datepart(MINUTE,@datetime)) UNION ALL
SELECT '%s', Convert(VARCHAR(2),Datepart(SECOND,@datetime))
SELECT @format = Replace(@format,token,VALUE)
FROM @token
ORDER BY id
RETURN @format
END
GO
-- Testing
SELECT formatted_datetime = dbo.Fn_format_date(Getdate(),fmt)
FROM (
SELECT fmt = '%yyyy.%mm.%dd %hh:%mi:%ss.%ms (%wd)'
UNION ALL
SELECT fmt = '%dd %mon %yy %hh:%mi:%ss.%ms (%wday)'
) f
formatted_datetime----------------------------------
2009.08.05 19:07:05.005 (Wed)
05 Aug 09 19:07:05.005 (Wednesday)
Of-course datetime formatting should still be performed at the front end application or reporting tool where the data is being displayed.