MsSQL: unexpected DATETIME column formatting style by CONACAT

Question

I faced something, which I cannot understand and cannot find the way to control it. I have a table in MsSQL DB with column A of datetime type. Simple select of A column shows this:

select A from dbo.xxx;
 
            A
-----------------------
1900-01-01 00:00:00.000
1900-01-01 14:00:18.123

But, when I use CONCAT like this:

select CONCAT('', A) from dbo.xxx;

I get results like:

Jan  1 1900 12:00AM
Jan  1 1900  2:00PM

I would like to get results as in first select (without CONCAT) and I would not like to use additional conversions like:

convert(VARCHAR, A, 121)

How can I influence CONCAT behaviour?

MsSQL: Microsoft SQL Server 2019 (RTM-CU20) (KB5024276) - 15.0.4312.2 (X64) Apr 1 2023 12:10:46 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 20.04.6 LTS)

Answer 1

CONCAT function use the default conversion of data for which DATETIME and SMALLDATETIME (that Microsoft advice you to change for DATE/DATETIME2...) are formatted as US-english style, while it is not the case for DATETIME2 or DATE that uses ISO SQL default format AAAA-MM-JJ...

Just CAST oor CONVERT those old style datatype to new on the fly and the expectd result will be correct :

select CONCAT('', CAST(A AS DATETIME2(0))) from dbo.xxx;