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

Posted on

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 ( 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:

  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 :

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:


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 Replace(Convert(nvarchar(100),YourDate,106),’ ‘,”) gives the desired result
enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *