How to get the date part from a date with timestamp column in SQL Server?

Question

I want to get the date in DDMMYYY format from a field that stores date in YYYY-MM-DD Timestamp format. (For example, '2018-11-09' should display '09Nov2018').

I know that one way is to use the datepart function (https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017) which can extract the year, month and day individually and then I can concat them.

Edit: I don't want to use getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using:

concat
(
 CONVERT(varchar, DATEPART(dd,columndate)), 
 CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) , 
 CONVERT(varchar, DATEPART(year, columndate))
) AS trial

This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to `datetype' again as that is how I want the result.

Is there any other way to achieve this?

Any ideas/suggestions much appreciated.

• Aaron Bertrand: If the data type is date, then it doesn't store it in any format, that is just how SSMS chooses to show it to you.
• Tibor Karaszi: In general, you can use either the CONVERT() function with a suitable formatting code, and massage the result to your linking (chop off the beginning, end, replace unwanted characters with nothing etc). Or use FORMAT() with which you can specify a "pattern" for the returned string. Format uses more CPU, though.
• Aaron Bertrand: How are you using CONCAT() in SQL Server 2008? Also, please read this about your convert() calls.
• T.H.: I don't know how it is working but I tried and it worked.
• Aaron Bertrand: Then you're not using SQL Server 2008 (which is how you tagged your question), since CONCAT() was introduced in SQL Server 2012.

Answer 1

Here is an example using GETDATE():

SELECT REPLACE(CONVERT(char(11), GETDATE(), 13),' ','');

Yields 12Nov2018. Three days ago, it would yield 09Nov2018.

To run this against your table:

SELECT Trial = REPLACE(CONVERT(char(11), YOUR_COLUMN_NAME_HERE, 13),' ','')
 FROM dbo.YOUR_TABLE_NAME_HERE;

In SQL Server 2012 and above, you can use FORMAT() but, like Tibor suggested, this uses more CPU (roughly doubling runtime in my tests).

SELECT FORMAT(GETDATE(), 'ddMMMyyyy');

• T.H.: Thank you for the answer but I can't use this as my reference date is not getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using concat(CONVERT(varchar, DATEPART(dd,columndate)), CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) , CONVERT(varchar, DATEPART(year, columndate))) AS trial. This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to datetype again as that is how I want the result.
• Aaron Bertrand: @T.H. you replace GETDATE() in my statement with the column name in your query. Your question didn't tell us either the name of the column or the name of the table, so I showed a sample that worked against a function instead.
• T.H.: Apologies about that, I will update the question. Thanks a lot.

Answer 2

SELECT Replace(Convert(nvarchar(100),YourDate,106),' ','') gives the desired result enter image description here