Format Date as String

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown

Date: 22 Jun 2012

Description

Formats a DATETIME parameter as a date string

e.g. 2012-06-22 13:54:39.993 becomes 'Friday, 22nd Jun 2012'

Good for producing nicely formatted reports

Code

DROP FUNCTION dbo.udf_FullDatetoString

GO

CREATE FUNCTION dbo.udf_FullDatetoString (@Date DATETIME)

RETURNS VARCHAR(30)

AS

/*****************************************************

Purpose. Converts standard datetime value

to a formatted date string

e.g. 2012-06-22 13:54:39.993 becomes 'Friday, 22nd Jun 2012'

Ideal for reports

Author: Unknown

History: 22 Jun 2012 - Initial Release

******************************************************/

BEGIN

-- not a pretty command, but it doesn't really fit well to a page.

RETURN

DATENAME(dw,GETDATE()) +', '

+ STUFF(CONVERT(CHAR(11),GETDATE(),106),3,0, SUBSTRING(

'stndrdthththththththththththththththththstndrdthththththththst '

,(DATEPART(DAY,GETDATE())*2)-1,2))

END

-- select dbo.udf_FullDatetoString (GETDATE())