Convert datetime to nvarchar but keep format

Question

I am returning either a DATETIME or the NVARCHAR = 'MULTIPLE' depending on whether or not an action has been performed more than one time.

So I am trying to store the DATETIME in its normal format '2012-10-23 13:59:47.000' but as an NVARCHAR. SQL wants to make it 'Oct 23 2012 12:40PM' How can I do this?

Right now I am doing:

CAST(r.Date_And_Time) AS NVARCHAR(30))

Answer 1

Use CONVERT. It has format parameter.

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

CONVERT(NVARCHAR(23), r.Date_And_Time, 121)

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

Answer 2

Declare @CreatedDate datetime
Select @CreatedDate='20121210'
Select CONVERT(VARCHAR,@createdDate, 21)