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.
Best Answer
Here is an example using
GETDATE()
:Yields
12Nov2018
. Three days ago, it would yield09Nov2018
.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).