I'm trying to convert a datetime column to ISO format, as yyyymmdd. For example, I would like to be able to convert '13 dec 2018' to '20181213'.

According to Microsoft's T-SQL Docs, I should be able to do this using

convert(nvarchar(8), '13 dec 2018', 112)

however this doesn't work - I get the result '13 dec 2', which looks to be nothing more than the original string cut down to 8 characters.

I have also tried using

convert(datetime, '13 dec 2018', 112)

which gives me the result of 'Dec 13 2018 12:00AM' - again, nothing like what the function is supposed to produce.

What I am doing wrong? I could solve the problem easily enough using datepart() and concatenated strings, but I'd rather use the more elegant approach if possible.

4 Answers

Billious On

I figured it out - because I was entering the date as a string, and not converting it to a datetime value first, it was treating my date as if it were a string. When I handle it this way:

convert(nvarchar(8), cast('13 dec 2018' as datetime), 112)

I get the expected result, '20181213'.

Gordon Linoff On

Combine them:

convert(nvarchar(8), convert(datetime, '13 dec 2018'), 112)

I don't recommend using the format 112 for the first conversion, because it is misleading. SQL Server is very good at converting without a format. If you do use one the appropriate one is 106.

Ravi Sharma On

select convert(nvarchar(8), cast(GETDATE() as datetime), 112) as TodayDate

enter image description here

Community On

YYYYMMDD format can obtain by datetime to varchar format.

select convert(varchar(8),convert(datetime, '13 dec 2018'),112)

The above query provides the required result