Strange CONVERT behaviour

Question

Strange CONVERT behaviour

Josh Ashwood 96

SELECT CONVERT(datetime, '01JAN2022', 103)

Can anyone explain to me how the above code works without error and returns '2022-01-01 00:00:00.000' when the specified style for 103 is dd/mm/yyyy ?

Shouldn't this code error?

0 comments

Answer accepted by question author

Erland Sommarskog 134.7K MVP Volunteer Moderator

I find that

SELECT CONVERT(datetime, '20221114', 103)

also works. To note here is that YYYYMMDD is a "safe" format which is always interpreted in the same way.

I guess that the conversion considers all formats, no matter the code. But if the format is an ambiguous one, the style code is used to force the interpretation. For instance:

SELECT CONVERT(datetime, '2022-04-06', 103)

returns 2022-06-04 00:00:00.000. Because with dmy format, SQL Server interprets this format as ydm.

By the way, I did not now that the format 01JAN2022 (without any spaces) would convert at all!

Josh Ashwood 96 Reputation points

2023-07-26T21:43:21.7+00:00

Thanks Erland

The documentation is really... absent... regarding this behaviour.

I guess there is nothing to say that this behaviour might not change in the future?

It's very weird - after all these years in SQL Server painstakingly ensuring the right style code is specified during conversions from string to date types, I find out that it only applies if there is ambiguity.

And yes you are right a format '01JAN2022' would be ddmonyyy - and there's no style code at all to specify that format!

Cheers
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-07-26T21:50:44.16+00:00

The documentation is really... absent... regarding this behaviour.

Yeah, I read the topic on CAST and CONVERT to see if there was something. But you may notice that pen in the upper right corner? You can use that to file issues on the documentation. Although, personally I may feel that this falls under "you cannot document everything".

I guess there is nothing to say that this behaviour might not change in the future?

Accidents have happened before, but I guess they would soon revert because I have a feeling that code may break in more than one place if they change something.

And yes you are right a format '01JAN2022' would be ddmonyyy - and there's no style code at all to specify that format!

Correct. But there is the PARSE function if you want to enforce a certain format. (Or you have some crazy format that CONVERT never gets right.)

1 additional answer

Your answer

Josh Ashwood 96 Reputation points

2023-07-26T21:43:21.7+00:00

Thanks Erland

The documentation is really... absent... regarding this behaviour.

I guess there is nothing to say that this behaviour might not change in the future?

It's very weird - after all these years in SQL Server painstakingly ensuring the right style code is specified during conversions from string to date types, I find out that it only applies if there is ambiguity.

And yes you are right a format '01JAN2022' would be ddmonyyy - and there's no style code at all to specify that format!

Cheers
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-07-26T21:50:44.16+00:00

The documentation is really... absent... regarding this behaviour.

Yeah, I read the topic on CAST and CONVERT to see if there was something. But you may notice that pen in the upper right corner? You can use that to file issues on the documentation. Although, personally I may feel that this falls under "you cannot document everything".

I guess there is nothing to say that this behaviour might not change in the future?

Accidents have happened before, but I guess they would soon revert because I have a feeling that code may break in more than one place if they change something.

And yes you are right a format '01JAN2022' would be ddmonyyy - and there's no style code at all to specify that format!

Correct. But there is the PARSE function if you want to enforce a certain format. (Or you have some crazy format that CONVERT never gets right.)

Answer 1

Yitzhak Khabinsky 27,196

Hi @Josh Ashwood,

The CONVERT() function third parameter is a style for an input value to allow interpretation of the '01JAN2022'.

SQL Server stores datetime value in a binary format internally. Its presentation is always in the ISO 8601 standard format, i.e yyyy-MM-dd hh:mm:ss.msec

You can use FORMAT() function to convert datetime data type to a string with a desired format.

0 comments

Share via

Strange CONVERT behaviour

1 additional answer

Your answer