Problem to convert a char(24) field to datetime

  • 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 ?

  • 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

  • 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