2

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.

|improve this question
  • 1
    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. – Aaron Bertrand♦ Nov 12 '18 at 13:37
  • 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. – Tibor Karaszi Nov 12 '18 at 13:41
  • How are you using CONCAT() in SQL Server 2008? Also, please read this about your convert() calls. – Aaron Bertrand♦ Nov 12 '18 at 14:37
  • I don't know how it is working but I tried and it worked. – T.H. Nov 12 '18 at 15:57
  • Then you're not using SQL Server 2008 (which is how you tagged your question), since CONCAT() was introduced in SQL Server 2012. – Aaron Bertrand♦ Nov 12 '18 at 17:10
5

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');
|improve this answer
  • 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. – T.H. Nov 12 '18 at 13:50
  • 1
    @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. – Aaron Bertrand♦ Nov 12 '18 at 13:52
  • Apologies about that, I will update the question. Thanks a lot. – T.H. Nov 12 '18 at 14:01
0

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

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.