July 15, 2010 at 8:33 am
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 ?
July 15, 2010 at 8:52 am
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
July 15, 2010 at 11:57 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply