When attempting to interact with column in a SQL Server table of type DATETIME , a certain row seems to parse incorrectly.
The DATETIME is initially represented in the format dd:mm:yyyy hh:ss , with the value parsing incorrectly being 12/6/2022 22:06 , when attempting to interact with this value in any way ( CAST , CONVERT , etc) it will parse into some form of the date '2022-12-07T03:06:00' .
This is the only value ( '12/6/2022 22:06' ) out of 600 entries I can find parsing incorrectly.
I unfortunately only have read access to this database.
I have tried to force a conversion of this value into a text ( nvarchar ), or a different DATE format but the date gets parsed regardless for example:
CAST(dateoccurred AS varchar(19))will become 'Dec 7 2022 3:06AM' .
Is there a conversion function to force the value initially represented in query to a text format that I can parse later down the data pipeline?
The version used is Microsoft SQL Server 2019.
2条答案
按热度按时间dfuffjeb1#
To reliably convert datetime to text and back use ISO 8601 format, which is style 126 in CONVERT
The text will look like
stszievb2#
The problem ended up being that there was a discrepancy in the formats, the dates originally shown in the web app was of the format mm:dd:yyyy, but the dates pulled from the api were in yyyy:mm:dd, in conjunction with a time zone difference.