CODEWITHSUNDEEP
sql-servert-sqldatedatetime
Chris Disley
Chris Disley

Reputation: 1345

SQL Server Specific Date Format DD MM YYYY

I have a date formatting requirement that initially looked straightforward, but is proving to be a pain. I'm using SQL Server 2012, but need to support 2008.

Looking at reference (https://msdn.microsoft.com/en-GB/library/ms187928.aspx) I can use CONVERT to format a datetime as a string in a lot of different formats, but I can't seem to get DD MM YYYY without workarounds. Is there a code that's not included on that reference that I'm missing somewhere?

Workarounds I can see are as follows, but I'm obviously not keen to use these if there's a sensible way to do it with T-SQL.

SELECT REPLACE(CONVERT(varchar, GETDATE(), 103), '/',' ')
SELECT RIGHT('0' + CONVERT(varchar, DATEPART(dd, GETDATE())), 2) + ' ' + RIGHT('0' + CONVERT(varchar, DATEPART(mm, GETDATE())), 2) + ' ' + CONVERT(varchar, DATEPART(yyyy, GETDATE()))

Following comments, an example input date would be 21st June 2016 (year = 2016, month = 06, day = 21), for which the desired output would be 21 06 2016.

Upvotes: 3

Views: 7455

Answers (2)

Pred
Pred

Reputation: 9042

The date format DD MM YYYY is not an 'official' format (with only spaces and no periods), therefore there is no format code for it in SQL Server.

You can use the workaround, you mentioned in the question or the FORMAT function in SQL Server 2012 or above: FORMAT(GETDATE(), N'dd MM yyyy').

There are other workarounds, but none of them are beautiful, including this contraption: RIGHT('0' + CONVERT(VARCHAR(2), DAY(@date)), 2) + ' ' + RIGHT('0' + CONVERT(VARCHAR(2), MONTH(@date)), 2) + ' ' + CONVERT(VARCHAR(4), YEAR(@date))

Another option is to create a CLR function and use .NET to format dates. CLRs are available since 2005.

You can create a calendar table and add a field with the desired format. You can join this table to get the formatted string for each date.

In general, formatting the result is not the SQL Server's responsibility, move this into the application which is using the data.

Upvotes: 2

Rich Benner
Rich Benner

Reputation: 8113

I'd go with your first suggestion, seems the most appropriate;

Making some test data;

IF OBJECT_ID('tempdb..#Dates') IS NOT NULL DROP TABLE #Dates
GO
CREATE TABLE #Dates (OriginalDate datetime)
INSERT INTO #Dates (OriginalDate)
VALUES
('2016-01-01')
,('2016-05-06')
,('2016-08-09')
,('2016-12-25')

Query;

SELECT
OriginalDate
,REPLACE(CONVERT(varchar,OriginalDate,103),'/',' ') NewDate
FROM #Dates

Result;

OriginalDate                NewDate
2016-01-01 00:00:00.000     01 01 2016
2016-05-06 00:00:00.000     06 05 2016
2016-08-09 00:00:00.000     09 08 2016
2016-12-25 00:00:00.000     25 12 2016

Upvotes: 2

Related Questions