![Illustration-Of-A-3d-Ivory-White-Man-Construction-Worker-Carrying-A-Wrench Illustration-Of-A-3d-Ivory-White-Man-Construction-Worker-Carrying-A-Wrench](http://lh5.ggpht.com/-nPmNTMNW1Sk/UkGzICqLLgI/AAAAAAAAA_c/JJasH8vsw1I/Illustration-Of-A-3d-Ivory-White-Man-Construction-Worker-Carrying-A-Wrench_thumb%25255B1%25255D.jpg?imgmax=800)
I found this looking for something else on the Internet, but I thought it would be a great item for your toolbox. The function was originally written for 2005, but it has worked on everything I’ve tried.
Enjoy!
Many thanks to Anubhav Goyal , who provided this information to the SQL community on June 11, 2009 - You rock Anubhav! http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/
/*-------------------------------------------------------------------------------------------*/
--SQL Server date formatting function --convert datetime to string
/*--------------------------------------------------------------------------------------------*/
--SQL datetime functions
--SQL Server date formats
--T-SQL convert dates
--Formatting dates sql server
CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @StringDate VARCHAR(32)
SET @StringDate = @FormatMask
IF (CHARINDEX ('YYYY',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'YYYY',
DATENAME(YY, @Datetime))
IF (CHARINDEX ('YY',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'YY',
RIGHT(DATENAME(YY, @Datetime),2))
IF (CHARINDEX ('Month',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'Month',
DATENAME(MM, @Datetime))
IF (CHARINDEX ('MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
SET @StringDate = REPLACE(@StringDate, 'MON',
LEFT(UPPER(DATENAME(MM, @Datetime)),3))
IF (CHARINDEX ('Mon',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'Mon',
LEFT(DATENAME(MM, @Datetime),3))
IF (CHARINDEX ('MM',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'MM',
RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
IF (CHARINDEX ('M',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'M',
CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
IF (CHARINDEX ('DD',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'DD',
RIGHT('0'+DATENAME(DD, @Datetime),2))
IF (CHARINDEX ('D',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'D',
DATENAME(DD, @Datetime))
RETURN @StringDate
END
GO
--Microsoft SQL Server date format function test
--MSSQL formatting dates
SELECT dbo.fnFormatDate (getdate(), 'MM/DD/YYYY') --01/03/2012
SELECT dbo.fnFormatDate (getdate(), 'DD/MM/YYYY') --03/01/2012
SELECT dbo.fnFormatDate (getdate(), 'M/DD/YYYY') --1/03/2012
SELECT dbo.fnFormatDate (getdate(), 'M/D/YYYY') --1/3/2012
SELECT dbo.fnFormatDate (getdate(), 'M/D/YY') --1/3/12
SELECT dbo.fnFormatDate (getdate(), 'MM/DD/YY') --01/03/12
SELECT dbo.fnFormatDate (getdate(), 'MON DD, YYYY') --JAN 03, 2012
SELECT dbo.fnFormatDate (getdate(), 'Mon DD, YYYY') --Jan 03, 2012
SELECT dbo.fnFormatDate (getdate(), 'Month DD, YYYY') --January 03, 2012
SELECT dbo.fnFormatDate (getdate(), 'YYYY/MM/DD') --2012/01/03
SELECT dbo.fnFormatDate (getdate(), 'YYYYMMDD') --20120103
SELECT dbo.fnFormatDate (getdate(), 'YYYY-MM-DD') --2012-01-03
--CURRENT_TIMESTAMP returns current system date and time in standard internal format
SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,'YY.MM.DD') --12.01.03
GO