Problem to convert a char(24) field to datetime

Question

Post reply

Problem to convert a char(24) field to datetime

christophe barbier

SSC Veteran

Points: 218
More actions
July 15, 2010 at 8:33 am

#223398

Hello,
I read many topics but don't find any answer.
I have a table in which one of the fields have char(24) data type to store dates+HH+MM+SS+mmmm.
Example top(10) rows using the following query to test if it's a good date format :
select top(10)CounterDateTime, IsDate(CounterDateTime) from CounterData
2010-07-06 21:00:01.5720
2010-07-06 21:00:31.4630
2010-07-06 21:01:01.4790
2010-07-06 21:01:31.4790
It seems it isn't in a good format.
But when I copy one of the example and try this select '2010-06-28 21:08:27.590',isdate('2010-06-28 21:08:27.590')
2010-06-28 21:08:27.5901
In this query, the field is recognize as a datetime format.
I do all these tests because I want to convert the date from char(24) to datetime format using this query :
insert into CounterDataP (GUID,CounterID,RecordIndex,CounterDateTime,CounterValue,FirstValueA,
FirstValueB,SecondValueA,SecondValueB,MultiCount)
select GUID,CounterID,RecordIndex,convert(datetime,CounterDateTime,121),CounterValue,FirstValueA,
FirstValueB,SecondValueA,SecondValueB,MultiCount from CounterData
Is anybody can help me please ?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95409 More actions · Answer 1

When I run this:

declare @TestTable table (CounterDateTime char(24))

insert into @TestTable

SELECT '2010-07-06 21:00:01.572' UNION ALL

SELECT '2010-07-06 21:00:31.463' UNION ALL

SELECT '2010-07-06 21:01:01.479' UNION ALL

SELECT '2010-07-06 21:01:31.479'

select CounterDateTime, IsDate(CounterDateTime) from @TestTable

I get all ones back from the IsDate function. Since you've got this in a char(24), but it's only using 23, I wonder if you've got an invisible character in the last position.

To test this, try running this code:

;WITH

Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),

Hundreds (N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2),

Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Hundreds),

CTE AS (

SELECT CounterDateTime,

N,

[CHAR] = substring(CounterDateTime, N, 1),

[ASCII] = ASCII(substring(CounterDateTime, N, 1))

FROM @TestTable -- change to your table

CROSS JOIN Tally

WHERE Tally.N < 25

)

SELECT *

FROM CTE

WHERE [ASCII] NOT BETWEEN 48 AND 57 -- 0-9

AND [ASCII] NOT IN (32,45,46,58) -- spaces, dashes, period, colon

ORDER BY CounterDateTime, N

It will display any characters that contains anything other than 0-9, spaces, dashes, periods or colons. It might help you to figure out what is going on. Ensure that you change it where indicated to point to your table.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

christophe barbier SSC Veteran Points: 218 More actions · Answer 2

Thanks a lot. I're rigth.

I haven't use your query yet, but I kepp it for later.

I change my query with this to test quickly and it's worked fine. The last char is effectively the problem.

convert(datetime,substring(CounterDateTime,1,23)

Thanks a lot.