CREATE FUNCTION dbo.FormatDateTime
(
@dt DATETIME,
@format VARCHAR(16)
)
RETURNS VARCHAR(64)
AS
BEGIN
DECLARE @dtVC VARCHAR(64)
SELECT @dtVC = CASE @format
WHEN 'LONGDATE' THEN
DATENAME(dw, @dt)
+ ',' + SPACE(1) + DATENAME(m, @dt)
+ SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2))
+ ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))
WHEN 'LONGDATEANDTIME' THEN
DATENAME(dw, @dt)
+ ',' + SPACE(1) + DATENAME(m, @dt)
+ SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2))
+ ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))
+ SPACE(1) + RIGHT(CONVERT(CHAR(20),
@dt - CONVERT(DATETIME, CONVERT(CHAR(8),
@dt, 112)), 22), 11)
WHEN 'SHORTDATE' THEN
LEFT(CONVERT(CHAR(19), @dt, 0), 11)
WHEN 'SHORTDATEANDTIME' THEN
REPLACE(REPLACE(CONVERT(CHAR(19), @dt, 0),'AM', ' AM'), 'PM', ' PM')
WHEN 'UNIXTIMESTAMP' THEN
CAST(DATEDIFF(SECOND, '19700101', @dt) AS VARCHAR(64))
WHEN 'YYYYMMDD' THEN
CONVERT(CHAR(8), @dt, 112)
WHEN 'YYYY-MM-DD' THEN
CONVERT(CHAR(10), @dt, 23)
WHEN 'YYMMDD' THEN
CONVERT(VARCHAR(8), @dt, 12)
WHEN 'YY-MM-DD' THEN
STUFF(STUFF(CONVERT(VARCHAR(8), @dt, 12), 5, 0, '-'), 3, 0, '-')
WHEN 'MMDDYY' THEN
REPLACE(CONVERT(CHAR(8), @dt, 10), '-', SPACE(0))
WHEN 'MM-DD-YY' THEN
CONVERT(CHAR(8), @dt, 10)
WHEN 'MM/DD/YY' THEN
CONVERT(CHAR(8), @dt, 1)
WHEN 'MM/DD/YYYY' THEN
CONVERT(CHAR(10), @dt, 101)
WHEN 'DDMMYY' THEN
REPLACE(CONVERT(CHAR(8), @dt, 3), '/', SPACE(0))
WHEN 'DD-MM-YY' THEN
REPLACE(CONVERT(CHAR(8), @dt, 3), '/', '-')
WHEN 'DD/MM/YY' THEN
CONVERT(CHAR(8), @dt, 3)
WHEN 'DD/MM/YYYY' THEN
CONVERT(CHAR(10), @dt, 103)
WHEN 'HH:MM:SS 24' THEN
CONVERT(CHAR(8), @dt, 8)
WHEN 'HH:MM 24' THEN
LEFT(CONVERT(VARCHAR(8), @dt, 8), 5)
WHEN 'HH:MM:SS 12' THEN
LTRIM(RIGHT(CONVERT(VARCHAR(20), @dt, 22), 11))
WHEN 'HH:MM 12' THEN
LTRIM(SUBSTRING(CONVERT(
VARCHAR(20), @dt, 22), 10, 5)
+ RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3))
ELSE
'Invalid format specified'
END
RETURN @dtVC
END
GO
====================================================================================
====================================================================================
DECLARE @now DATETIME
SET @now = GETDATE()
PRINT dbo.FormatDateTime(@now, 'LONGDATE')
PRINT dbo.FormatDateTime(@now, 'LONGDATEANDTIME')
PRINT dbo.FormatDateTime(@now, 'SHORTDATE')
PRINT dbo.FormatDateTime(@now, 'SHORTDATEANDTIME')
PRINT dbo.FormatDateTime(@now, 'UNIXTIMESTAMP')
PRINT dbo.FormatDateTime(@now, 'YYYYMMDD')
PRINT dbo.FormatDateTime(@now, 'YYYY-MM-DD')
PRINT dbo.FormatDateTime(@now, 'YYMMDD')
PRINT dbo.FormatDateTime(@now, 'YY-MM-DD')
PRINT dbo.FormatDateTime(@now, 'MMDDYY')
PRINT dbo.FormatDateTime(@now, 'MM-DD-YY')
PRINT dbo.FormatDateTime(@now, 'MM/DD/YY')
PRINT dbo.FormatDateTime(@now, 'MM/DD/YYYY')
PRINT dbo.FormatDateTime(@now, 'DDMMYY')
PRINT dbo.FormatDateTime(@now, 'DD-MM-YY')
PRINT dbo.FormatDateTime(@now, 'DD/MM/YY')
PRINT dbo.FormatDateTime(@now, 'DD/MM/YYYY')
PRINT dbo.FormatDateTime(@now, 'HH:MM:SS 24')
PRINT dbo.FormatDateTime(@now, 'HH:MM 24')
PRINT dbo.FormatDateTime(@now, 'HH:MM:SS 12')
PRINT dbo.FormatDateTime(@now, 'HH:MM 12')
PRINT dbo.FormatDateTime(@now, 'goofy')
(((*****)))
http://sqlserver2000.databases.aspfaq.com/can-i-make-sql-server-format-dates-and-times-for-me.html