| SQLTutorials | |
| Powered By PhICK1.com | |
SQL FormatDateTime FunctionSQL FormatDateTime Function is use to format a different type of date time format that is not available in SQL Cast and convert for DateTime.DECLARE @Date DATETIME DECLARE @format VARCHAR(64) SET @Date = GETDATE() DECLARE @DateResult VARCHAR(64) SELECT @DateResult = CASE @format WHEN 'LONGDATE' THEN DATENAME(dw, @Date) + ',' + SPACE(1) + DATENAME(m, @Date) + SPACE(1) + CAST(DAY(@Date) AS VARCHAR(2)) + ',' + SPACE(1) + CAST(YEAR(@Date) AS CHAR(4)) WHEN 'LONGDATEANDTIME' THEN DATENAME(dw, @Date) + ',' + SPACE(1) + DATENAME(m, @Date) + SPACE(1) + CAST(DAY(@Date) AS VARCHAR(2)) + ',' + SPACE(1) + CAST(YEAR(@Date) AS CHAR(4)) + SPACE(1) + RIGHT(CONVERT(CHAR(20), @Date - CONVERT(DATETIME, CONVERT(CHAR(8), @Date, 112)), 22), 11) WHEN 'SHORTDATE' THEN LEFT(CONVERT(CHAR(19), @Date, 0), 11) WHEN 'SHORTDATEANDTIME' THEN REPLACE(REPLACE(CONVERT(CHAR(19), @Date, 0), 'AM', ' AM'), 'PM', ' PM') WHEN 'YYYY-MM-DD' THEN CONVERT(CHAR(10), @Date, 23) END SELECT @DateResult If Format is SET @format = 'YYYY-MM-DD' , Result : 2007-11-26 SET @format = 'LONGDATE' , Result : Monday, November 26, 2007 SET @format = 'LONGDATEANDTIME' , Result : Monday, November 26, 2007 10:01:13 PM SET @format = 'SHORTDATE' , Result : Nov 26 2007 SET @format = 'SHORTDATEANDTIME' , Result : Nov 26 2007 10:02 PM Below is the SQL Function Code for FormatDateTime Function CREATE FUNCTION dbo.FormatDateTime ( @Date DATETIME, @format VARCHAR(16) ) RETURNS VARCHAR(64) AS BEGIN DECLARE @DateResult VARCHAR(64) SELECT @DateResult = CASE @format WHEN 'LONGDATE' THEN DATENAME(dw, @Date) + ',' + SPACE(1) + DATENAME(m, @Date) + SPACE(1) + CAST(DAY(@Date) AS VARCHAR(2)) + ',' + SPACE(1) + CAST(YEAR(@Date) AS CHAR(4)) WHEN 'LONGDATEANDTIME' THEN DATENAME(dw, @Date) + ',' + SPACE(1) + DATENAME(m, @Date) + SPACE(1) + CAST(DAY(@Date) AS VARCHAR(2)) + ',' + SPACE(1) + CAST(YEAR(@Date) AS CHAR(4)) + SPACE(1) + RIGHT(CONVERT(CHAR(20), @Date - CONVERT(DATETIME, CONVERT(CHAR(8), @Date, 112)), 22), 11) WHEN 'SHORTDATE' THEN LEFT(CONVERT(CHAR(19), @Date, 0), 11) WHEN 'SHORTDATEANDTIME' THEN REPLACE(REPLACE(CONVERT(CHAR(19), @Date, 0), 'AM', ' AM'), 'PM', ' PM') WHEN 'YYYY-MM-DD' THEN CONVERT(CHAR(10), @Date, 23) END RETURN @DateResult END powered by www.pHick1.com |
|