Why does this char value not convert to datetime?

Question

I have an instance of Performance Monitor saving data to a table on a MS SQL server database. The performance data is saved to a table called CounterData. The table has a char(24) field (counterdatetime) for storing the date/time of the data. I'm trying to treat that field as a datetime data, but I keep getting "Conversion failed when converting date and/or time from character string."

Here is an example of data from the counterdatetime: 2019-07-02 09:19:46.300

I have tried different variations of CAST and CONVERT to allow me to handle this data as date time. For example:

SELECT CAST(counterdatetime as datetime) from CounterData
SELECT CAST(counterdatetime as datetime2) from CounterData
SELECT CONVERT(datetime,counterdatetime,101) from CounterData

Each gives me the "Conversion failed . . ." error. I thought perhaps the failure was triggered by a bogus value in one record, so I tried limiting the conversion with a WHERE expression that specifies a good value. I also tried casting the value itself, as in:

SELECT CAST('2019-07-02 09:19:46.300' as datetime)

In this instance, the CAST works.

Why doesn't it work when I select the value from the table?

UPDATE

@Jeroen Mostert provided the answer in a comment, which I posted as an answer.

Answer 1

"Don't do that!" is not an option! The table was produced by Performance monitor and the exact same is produced by the RELOG.exe tool that imports the Performance monitor data from a file. Once you invent a time machine, feel free to go back to the 90s and tell Microsoft not to use varchar to store a date.

It's possible to convert the value to date with:

CONVERT(datetime2(3), convert(varchar(23), CounterDateTime), 121)

…but of course it's inefficient to do it all the time and it would prevent you from adding a useful index on the data.

The best solution I found was to add a persisted computed column with the date converted to datetime2(3), rename the table, create a view containing only the columns expected by RELOG.exe and use the table and only the converted value.

EXEC sp_rename 'dbo.CounterData.CounterDateTime', 'CounterDateTime_raw';
go

ALTER TABLE CounterData ADD CounterDateTime AS CONVERT(datetime2(3), convert(varchar(23), CounterDateTime_raw), 121) PERSISTED;
go

EXEC sp_rename 'dbo.CounterData', 'CounterDataToUse';
go

CREATE VIEW dbo.CounterData 
AS
SELECT [GUID], [CounterID], [RecordIndex], [CounterDateTime_raw] as [CounterDateTime], [CounterValue], [FirstValueA], [FirstValueB], [SecondValueA], [SecondValueB], [MultiCount]
FROM dbo.CounterDataToUse
go

CREATE NONCLUSTERED INDEX IX_CounterDataConverted_RecordedAt_CounterID ON dbo.CounterDataToUse
    (
    CounterDateTime,
    CounterID
    ) 
    INCLUDE (CounterValue)
    WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

You just have to make sure to use the table and not the view!

Answer 2

@Jeroen Mostert provided the correct answer in a comment to the original post, so credit should go to him.

Uh-oh, Spaghetti-O's. That's a NUL character that doesn't belong there. These are extra awful because REPLACE doesn't know what to do with them by default. Try CONVERT(DATETIME, REPLACE(CounterDateTime COLLATE Latin1_General_BIN2, CHAR(0), '')). (SUBSTRING(CounterDateTime, 1, 23) should also work in this case, since we can expect the NUL to always be at the end.)

Answer 3

The table has a char(24) field... I'm trying to treat that field as a datetime data

DON'T DO THAT! If you have DateTime values, store them using a type from the DateTime family! Hopefully this whole thing is part of a project to fix (because right now, the schema really is broken) this table. As just one example (of many) of why this is important, indexes on this column are mostly useless as it is.

I thought perhaps the failure was triggered by a bogus value in one record...

This is exactly what is happening. There is bad data. (Using a real DateTime type would prevent this from happening in the first place).

... so I tried limiting the conversion with a WHERE expression that specifies a good value.

Check your WHERE clause again, because it wasn't specific enough, or there was a problem with the value you chose you didn't see at first.

Answer 4

I am not certain if the literal '2019-07-02 09:19:46.300' is the text value which is actually crashing your query. If you are using SQL Server 2012 or later, there is a helper function TRY_CONVERT which will attempt to convert an input string to a certain type (datetime in this case), and will either return a converted datetime value, or NULL if the conversion failed.

Try running the following query:

SELECT
    counterdatetime
FROM CounterData
WHERE
    TRY_CONVERT(datetime, counterdatetime) IS NULL;

If you see any records showing up in the result set, it means that these values cannot be successfully converted.

Answer 5

yyyy-MM-dd hh:mm:ss.sss is not an unambiguous format when using the datetime datatype; it changes depending on the language.

Take the below examples:

SET LANGUAGE ENGLISH; --American
SELECT CONVERT(datetime,'2019-07-06'); --Returns 06 July 2019

SET LANGUAGE BRITISH; --English
SELECT CONVERT(datetime,'2019-07-06'); --Returns 07 June 2019

If we then take a date like the below, watch what happens:

SET LANGUAGE ENGLISH; --American
SELECT CONVERT(datetime,'2019-01-31'); --Returns 01 January 2019

SET LANGUAGE British; --English
SELECT CONVERT(datetime,'2019-01-31'); --Conversion error

Really, however, you should not be storing your dates as a char, and you need to fix that. Fortunately, you can easily fix your data by changing it to the ISO format yyyy-MM-ddThh:mm:ss.sss:

UPDATE CounterData
SET counterdatetime = STUFF(counterdatetime,11,1,'T');

ALTER TABLE CounterData ALTER COLUMN counterdatetime datetime;