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.
date
, then it doesn't store it in any format, that is just how SSMS chooses to show it to you.CONCAT()
in SQL Server 2008? Also, please read this about yourconvert()
calls.CONCAT()
was introduced in SQL Server 2012.