Cannot convert valid Char(24) date/time to date/time data type
-
Hi,
I have collected some data using TYPEPERF.
This command creates tables in the database specified in a DSN.
The table with the data is CounterData.
CREATE TABLE [dbo].[CounterData]([GUID] [uniqueidentifier] NOT NULL,
[CounterID] [int] NOT NULL,
[RecordIndex] [int] NOT NULL,
[CounterDateTime] [char](24) NOT NULL,
[CounterValue] [float] NOT NULL,
[FirstValueA] [int] NULL,
[FirstValueB] [int] NULL,
[SecondValueA] [int] NULL,
[SecondValueB] [int] NULL,
[MultiCount] [int] NULL,
PRIMARY KEY CLUSTERED
( [GUID] ASC, [CounterID] ASC, [RecordIndex] ASC )
When querying this data i'm get an error converting the CounterDateTime (Char(24)) to any kind of date data type.
SELECTTOP 1CASE
WHEN ISDATE(CounterDateTime) = 1
THEN CAST(CounterDateTime AS SmallDateTime)
ELSE NULL
END Dt
FROMCounterData
Returns NULL
where as
SELECTTOP 1CASE
WHEN ISDATE(CounterDateTime) = 1
THEN CAST(CounterDateTime AS SmallDateTime)
ELSE CounterDateTime
END Dt
FROMCounterData
Returns:
Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.
Doesn't matter which date data type I use.
DateTime and DateTime2 return:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Converting Char(24) using variables works fine.
DECLARE @dt Char(24)SET @dt = '2013-09-02 23:59:23.363' --Copied straight from the table.
SELECT CAST(@DT AS SmallDateTIme)
SELECT CAST(@DT AS DateTIme)
SELECT CAST(@DT AS DateTIme2)
Returns:
2013-09-02 23:59:00
2013-09-02 23:59:23.363
2013-09-02 23:59:23.3630000
SELECTMIN(CounterDateTime) MinDT, MAX(CounterDateTime) MaxDTFROMCounterData
Returns:
MinDTMaxDT2013-08-30 23:59:25.1432013-09-03 10:20:56.640
Table contains no NULL values.
All values are between 2013-08-30 AND 2013-09-03.
What's going on here?
-
This is one of the problems with storing dates with the wrong data type. Not a criticism of you, since this probably isn't your design.
You only need one of the dates to be unconvertable to get that error. How many rows are in your table, and have you checked them all?
John
Edit: could you be falling foul of this? It's from the CASE documentation:
The data types of input_expression and each when_expression must be the same or must be an implicit conversion.
-
Hi John,
Thanks for taking the time to reply.
Table has some 766k rows and counting.
I don't think it has anything to do with the CASE statement.
Even this results in the same error.
SELECTTOP 1 CONVERT(DateTime, CounterDateTime)FROMCounterData
Both CAST and CONVERT generate these errors.
-
What does this return?
SELECTCounterDateTimeFROMCounterData
WHEREISDATE(CounterData) = 0
John
-
Returns all rows.
I'm thinking its a regional problem.
Trying to convert 2013-08-31 to 2013-31-08.
If tried some combinations of SET DATEFORMAT to no avail.
The only thing that seems to work is converting only a portion of the values.
SELECTTOP 1 CounterDateTime,CAST(LEFT(CounterDateTime, 16) AS SmallDateTime) DateVal
FROMCounterData
WHEREMONTH(CAST(LEFT(CounterDateTime, 16) AS SmallDateTime)) = 8
Returns:
CounterDateTimeDateVal2013-08-31 23:59:56.1522013-08-31 23:59:00
So i'll have to aggregate the data into another table before querying the data.
Wonder why MS decided to use a Char(24) instead of DateTime.....?
-
That suggests that the problem lies in what comes after the decimal point. What does this return?
SELECT LEN(CounterDateTime), COUNT(*)FROM CounterData
GROUP BY LEN(CounterDateTime)
John
-
Returns: 24 783000 (Current row count)
Seems to be the 24th char.
Unless SQL 2012 (Express) auto trims, I only count 23 chars in the original Char(24) column values.
SELECTTOP 5CAST(LEFT(CounterDateTime, 23) AS DateTime) LEFTVal,
CounterDateTime OrigVal
FROMCounterData
Returns
LEFTValOrigVal2013-09-02 23:59:53.3632013-09-02 23:59:53.363
2013-09-03 00:00:53.3632013-09-03 00:00:53.362
2013-09-03 00:01:53.3772013-09-03 00:01:53.376
2013-09-03 00:02:53.3772013-09-03 00:02:53.375
2013-09-03 00:03:53.3902013-09-03 00:03:53.389
But this results in the same error:
CAST(RTRIM(LTRIM(CounterDateTime)) AS SmallDateTime)This shows that there's no auto trimming happening:
SELECTTOP 5 REPLACE(CounterDateTime, ' ', '@')FROMCounterData
Returns:
2013-09-02@23:59:53.363
2013-09-03@00:00:53.362
2013-09-03@00:01:53.376
2013-09-03@00:02:53.375
2013-09-03@00:03:53.389
Maybe a LF or CRLF somewhere... Not sure if this query is correct, but no rows are returned.
SELECTTOP 10CounterDateTime
FROMCounterData
WHERECounterDateTime LIKE '%' + CHAR(10) + '%'
OR CounterDateTime LIKE '%' + CHAR(13) + '%'
This returns 24
SELECTMIN(LEN(CounterDateTime)) MinLenValFROMCounterData
-
Both of there return the same error.
SELECTTOP 1 CAST(REPLACE(CounterDateTime, CHAR(0), '') AS SmalldateTime)FROMCounterData
SELECTTOP 1 CAST(REPLACE(CounterDateTime, CHAR(9), '') AS SmalldateTime)
FROMCounterData
Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.
(Samething with DateTime)
-
How about this?
SELECTCAST(LEFT(CounterDateTime, 23) AS SmalldateTime)FROMCounterData
John
-
Dennis Post (9/3/2013)
Hi Sean,Welcome to the party.
Your query returns 0's. Testing for > 0 returns no rows.
This mean the last char is a NULL ??
Yes the last character is a NULL. The source data is probably a zero padded binary.
You can either SUBSTRING( 1, 23) or CAST( AS CHAR(23))
edit to add: Or LEFT as John suggested above.
-
Yeah that works (See earlier post).
Then the only way to get rid of this NULL character is by selecting LEFT(CounterDateTime, 23).
I haven't been able to REPLACE the CHAR(0) nor have I been able to detect it with:
SELECTTOP 1CounterDateTime ,
CASE
WHEN CHARINDEX(CHAR(0), CounterDateTime) > 0 THEN 'ASCII NULL found.'
WHEN CHARINDEX(CHAR(9), CounterDateTime) > 0 THEN 'ASCII TAB found.'
WHEN CHARINDEX(CHAR(10), CounterDateTime) > 0 THEN 'ASCII CR found.'
WHEN CHARINDEX(CHAR(13), CounterDateTime) > 0 THEN 'ASCII CRLF found.'
ELSE NULL
END
FROMCounterData
Thanks for brainstorming with me on this!! 🙂
EDIT : Char(10) = LF, CHAR(13) = CR
CAST(SUBSTRING(CounterDateTime, 1, 23) AS DateTime) works as well.
-
Dennis Post (9/3/2013)
I haven't been able to REPLACE the CHAR(0) nor have I been able to detect it with:Thanks for brainstorming with me on this!! 🙂
You're welcome.
The reason you cannot replace the NULL is that you are most likely using a windows collation. Windows collation "ignores" the NULL character while SQL collation does not. Try this:
CREATE TABLE Test1 (MyValue CHAR(2) COLLATE Latin1_General_CI_AS)CREATE TABLE Test2 (MyValue CHAR(2) COLLATE SQL_Latin1_General_CP1_CS_AS)
INSERT INTO Test1 VALUES ('x0'), ('x '), ('x' + CHAR(0))
INSERT INTO Test2 VALUES ('x0'), ('x '), ('x' + CHAR(0))
SELECT
REPLACE(MyValue, CHAR(0), 'y')
FROM
Test1
SELECT
REPLACE(MyValue, CHAR(0), 'y')
FROM
Test2
DROP TABLE Test1;
DROP TABLE Test2;
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply