<br />
SET QUOTED_IDENTIFIER ON <br />
GO<br />
SET ANSI_NULLS ON <br />
GO<br />
<br />
<br />
create FUNCTION dbo.udf_FormatDate (@date datetime, @format varchar(50)) <br />
RETURNS VARCHAR(50) AS <br />
BEGIN <br />
-------------------------------------------------------------------------------------<br />
--<br />
-- Name: FormatDate<br />
--<br />
-- Purpose: Mimics the VB Format routine for dates<br />
--<br />
-- Parameters: <br />
-- @date - Date, Date to be formatted<br />
-- @format - String, Template to format the date to<br />
--<br />
-- Returns: String, Date formatted to user requested template<br />
--<br />
-- Notes: <br />
-- 1. Time information is not accounted for in this routine<br />
--<br />
-- 2. @format accepts the following values for each section of the date.<br />
-- Day<br />
-- dddd - Full Day Name<br />
-- ddd - Abbreviated Day Name<br />
-- dd - Zero Padded Day Number<br />
-- d - Day Number<br />
--<br />
-- Month<br />
-- mmmm - Full Month Name<br />
-- mmm - Abbreviated Month Name<br />
-- mm - Zero Padded Month Number<br />
-- m - Month Number<br />
--<br />
-- Year<br />
-- yyyy - Full 4 digit year<br />
-- yy - 2 digit year<br />
--<br />
-- 3. Any unexpected characters will be returned in the string<br />
--<br />
-- 4. Assumes database was set up with case-insensitive collation<br />
--<br />
-- Example Usage:<br />
-- All examples use the following date 2003-07-13 00:00:00<br />
--<br />
-- 'dddd, mmmm dd, yyyy' --> Sunday, July 13, 2003<br />
-- 'mmddyyyy' --> 07132003<br />
-- 'm-d-yy' --> 7-13-03<br />
-- 'mm/dd/yyyy' --> 07/13/2003<br />
--<br />
-------------------------------------------------------------------------------------<br />
DECLARE @pos AS INTEGER<br />
DECLARE @char AS VARCHAR(1)<br />
--<br />
-- Replace Year<br />
--<br />
SET @pos = CHARINDEX('yyyy', @format)<br />
WHILE @pos > 0<br />
BEGIN<br />
SET @format = STUFF(@format, @pos, 4, DATENAME(yyyy, @date))<br />
--PRINT @format<br />
SET @pos = CHARINDEX('yyyy', @format)<br />
END<br />
<br />
SET @pos = CHARINDEX('yy', @format)<br />
WHILE @pos > 0<br />
BEGIN<br />
SET @format = STUFF(@format, @pos, 2, RIGHT(DATENAME(yyyy, @date) ,2))<br />
--PRINT @format<br />
SET @pos = CHARINDEX('yy', @format)<br />
END<br />
--<br />
-- Replace Month<br />
--<br />
SET @pos = CHARINDEX('mmmm', @format)<br />
WHILE @pos > 0<br />
BEGIN<br />
SET @format = STUFF(@format, @pos, 4, DATENAME(month, @date))<br />
--PRINT @format<br />
SET @pos = CHARINDEX('mmmm', @format)<br />
END<br />
<br />
SET @pos = CHARINDEX('mmm', @format)<br />
WHILE @pos > 0<br />
BEGIN<br />
SET @format = STUFF(@format, @pos, 3, LEFT(DATENAME(month, @date), 3))<br />
--PRINT @format<br />
SET @pos = CHARINDEX('mmm', @format)<br />
END<br />
SET @pos = CHARINDEX('mm', @format)<br />
WHILE @pos > 0<br />
BEGIN<br />
SET @format = STUFF(@format, @pos, 2, RIGHT(('0' + CAST(DATEPART(month, @date) AS VARCHAR(2))), 2))<br />
--PRINT @format<br />
SET @pos = CHARINDEX('mm', @format)<br />
END<br />
<br />
SET @pos = CHARINDEX('m', @format)<br />
WHILE @pos > 0<br />
BEGIN<br />
-- account for MArch and deceMBer<br />
SET @char = SUBSTRING(@format, @pos + 1, 1)<br />
IF (@char <> 'a') AND (@char <> 'b')<br />
BEGIN<br />
SET @format = STUFF(@format, @pos, 1, CAST(DATEPART(month, @date) AS VARCHAR(2)))<br />
--PRINT @format<br />
SET @pos = CHARINDEX('m', @format)<br />
END<br />
ELSE<br />
BEGIN<br />
SET @pos = CHARINDEX('m', @format, @pos + 1)<br />
END<br />
END<br />
--<br />
-- Replace Day<br />
--<br />
SET @pos = CHARINDEX('dddd', @format)<br />
WHILE @pos > 0<br />
BEGIN<br />
SET @format = STUFF(@format, @pos, 4, DATENAME(weekday, @date))<br />
--PRINT @format<br />
SET @pos = CHARINDEX('dddd', @format)<br />
END<br />
<br />
SET @pos = CHARINDEX('ddd', @format)<br />
WHILE @pos > 0<br />
BEGIN<br />
SET @format = STUFF(@format, @pos, 3, LEFT(DATENAME(weekday, @date), 3))<br />
--PRINT @format<br />
SET @pos = CHARINDEX('ddd', @format)<br />
END<br />
SET @pos = CHARINDEX('dd', @format)<br />
WHILE @pos > 0<br />
BEGIN<br />
SET @format = STUFF(@format, @pos, 2, RIGHT(('0' + DATENAME(day, @date)), 2))<br />
--PRINT @format<br />
SET @pos = CHARINDEX('dd', @format)<br />
END<br />
SET @pos = CHARINDEX('d', @format)<br />
WHILE @pos > 0<br />
BEGIN<br />
-- account for DEcember, sunDAy --> saturDAy, weDNesday<br />
SET @char = SUBSTRING(@format, @pos + 1, 1)<br />
IF (@char <> 'e') AND (@char <> 'a') AND (@char <> 'n')<br />
BEGIN<br />
SET @format = STUFF(@format, @pos, 1, CAST(DATEPART(day, @date) AS VARCHAR(2)))<br />
--PRINT @format<br />
<br />
<br />
SET @pos = CHARINDEX('d', @format)<br />
END<br />
ELSE<br />
BEGIN<br />
SET @pos = CHARINDEX('d', @format, @pos + 1)<br />
END<br />
END<br />
IF @format = '//' BEGIN<br />
SET @format = ''<br />
END<br />
RETURN @format<br />
END<br />
<br />
<br />
<br />
<br />
<br />
<br />
GO<br />
SET QUOTED_IDENTIFIER OFF <br />
GO<br />
SET ANSI_NULLS ON <br />
GO<br />
<br />
"People who never make mistakes, never do anything."
My Blog
|