August 25, 2009 at 2:59 pm
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.
August 25, 2009 at 3:27 pm
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'
August 25, 2009 at 3:39 pm
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.
August 25, 2009 at 3:52 pm
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
July 28, 2011 at 1:35 am
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.
July 28, 2011 at 8:04 am
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