One of those days.... datatype conversion error

  • I have been logging data from perfmon to a SQL Server DB. The Date/Time info is stored as CHAR(24).

    select CounterDateTime from dbo.counterdata where CounterID = 2 and RecordIndex = 2 returns the value '2009-08-24 10:44:14.048'.

    If I do select cast('2009-08-24 10:44:14.048' AS Datetime), I get a result, '2009-08-24 10:44:14.047'

    However if i just do

    select cast(CounterDateTime AS DATETIME) from dbo.counterdata where CounterID = 2 and RecordIndex = 2

    I get "Conversion failed when converting datetime from character string."

    If i do select CAST(LEFT(CounterDateTime,23) AS DATETIME) from dbo.counterdata where CounterID = 2 and RecordIndex = 2 it works fine.

    I presume this has something to do with the column being CHAR(24) and not VARCHAR(24). I am zoning on what the problem is here.

  • There must be something you're not mentioning, because this works fine:

    CREATE TABLE #Test

    (

    CounterID INT,

    RecordIndex INT,

    CounterDateTime CHAR(24)

    )

    INSERT INTO #Test (CounterID, RecordIndex, CounterDateTime)

    VALUES (2, 2, '2009-08-24 10:44:14.048')

    SELECT CAST(CounterDateTime AS DATETIME) FROM #Test WHERE CounterID = 2 AND RecordIndex = 2

    DROP TABLE #Test

    Returns a recordset with '2009-08-24 10:44:14.047'

  • I am all for having missed somethign obvious....... but i do not know what it is.

    I am logging perfmon data to a table.

    When do

    select cast(CounterDateTime AS DATETIME) from dbo.counterdata where CounterID = 2 and RecordIndex = 2

    I get

    Msg 241, Level 16, State 1, Line 1

    Conversion failed when converting datetime from character string.

  • You'll have to provide more than that if you want help, since like i said, just a quick test which i pasted above, shows that it works fine.

    There must be some records that are part of the result set which would exist from that query, which can't be converted into DATETIME.

    post table definitions, and the result set you would be getting if you just did SELECT CounterDateTime from dbo.counterdata WHERE CounterID = 2 AND RecordIndex = 2

  • This reply may be a bit late, but this morning I had the same problem, so I thought I'd post the answer for anybody else who stumbles upon this. The problem is that there is an extra character following the time value, ascii character 0. You can check this easily:

    SELECT DISTINCT ascii(SUBSTRING(counterdatetime, 24,1))

    FROMCounterData

    It will return only 0.

    Robert van den Berg

    Freelance DBA
    Author of:

  • The BEST solution BY FAR is to use proper datatypes. Don't store datetime data in a varchar column. It is frought with errors and has been the topic of so many threads on here it is impossible to count. There are no conversion errors when the data is stored as the correct datatype and your performance will benefit too because you don't have to convert/cast your data every single time you need it.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 6 posts - 1 through 5 (of 5 total)

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