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://docs.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.

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');

Answer 2

SELECT Replace(Convert(nvarchar(100),YourDate,106),' ','') gives the desired result