October 16, 2009 at 12:17 pm
Why does this fail?
I must be having a slow day today, because I am not seeing my error here...
FAILS:
SELECT CounterDateTime, CONVERT(DATETIME,[CounterData].[CounterDateTime], 121)
FROM [Counterdata]
WORKS:
SELECT CONVERT(DATETIME, '2009-10-15 22:24:40.465', 121)
What am I doing wrong here???
Can someone lend me a hand, or a firm swift kick in the head, because I must be missing something obvious here?
October 16, 2009 at 12:28 pm
Does it happen for all rows? Maybe there is a value that cannot be converted.
Regards
Piotr
...and your only reply is slàinte mhath
October 16, 2009 at 12:32 pm
If there is any value in your table (even if you're excluding it with a where clause) that cannot be converted to a datetime, it will fail.
It should give you the value that is failing in the error message.
October 16, 2009 at 12:35 pm
I will go and check the table for any values that don't seem to match.
Would I just do an isdate type deal to look for that?
All the values that I have seen conform to the string literal I used in the example that did work.
That was the first value in the table.
The complete error message:
"Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
"
October 16, 2009 at 12:44 pm
Try these:
SELECT * FROM CounterData
WHERE ISDATE(CounterDateTime) = 0
SELECT * FROM CounterData
WHERE PATINDEX('[12][0-9][0-9][0-9]-[01][0-9]-[0-3][0-9] [012][0-9]:[0-5][0-9]:[0-5][0-9].[0-9][0-9][0-9]',CounterDateTime) = 0
October 16, 2009 at 12:44 pm
If I may ask you about the design, why did you decide to keep varchar values instead of datetime in your table? I am almost certain conversion from datetime to varchar happens somewhere during the insert to this table.
Regards
Piotr
...and your only reply is slàinte mhath
October 16, 2009 at 12:53 pm
Ask the perfmon people at Microsoft.... bunch of jerks if you ask me! 🙂
Not my work, perfmon created the tables.
So I ran:
SELECT ISDATE(CounterDateTime)
FROM [CounterData]
WHERE ISDATE(CounterDateTime) = 1
Nothing, every record came back as a 0...???
The PatIndex query returns all records as well (didn't see that until after I posted, thanks for providing the query for that)
2009-10-15 22:24:10.464
2009-10-15 22:24:25.465
2009-10-15 22:24:40.465
2009-10-15 22:24:55.450
2009-10-15 22:25:10.467
2009-10-15 22:25:25.467
2009-10-15 22:25:40.452
2009-10-15 22:25:55.453
2009-10-15 22:26:10.469
2009-10-15 22:26:25.454
First 10 rows from the table, I don't get why that format is NOT valid?
October 16, 2009 at 1:59 pm
The Patindex should only return the values which do NOT comply.
I have a new theory. Run the following, post the results.
SELECT COUNT(*) FROM CounterData
WHERE PATINDEX('[12][0-9][0-9][0-9]-[01][0-9]-[0-3][0-9]%[012][0-9]:[0-5][0-9]:[0-5][0-9].[0-9][0-9][0-9]',CounterDateTime) = 0
SELECT COUNT(*) FROM CounterData
WHERE PATINDEX('[12][0-9][0-9][0-9]-[01][0-9]-[0-3][0-9]_[012][0-9]:[0-5][0-9]:[0-5][0-9].[0-9][0-9][0-9]',CounterDateTime) = 0
SELECT COUNT(*) FROM CounterData
WHERE PATINDEX('[12][0-9][0-9][0-9]-[01][0-9]-[0-3][0-9] [012][0-9]:[0-5][0-9]:[0-5][0-9].[0-9][0-9][0-9]',CounterDateTime) = 0
SELECT COUNT(*) FROM CounterData
October 16, 2009 at 3:43 pm
I may not check this again for a while after you respond (SQL Saturday tomorrow!), so my theory is that you have a hidden character (likely char(10), char(13) or char(9) ) in the string. SSMS / QA do not show these characters, they just show a space which is why everything looks normal to you.
You should search for those characters in your dates.
October 16, 2009 at 7:49 pm
Would outputting to a text file and then opening it in something like TextPad allow me to see such a character?
October 18, 2009 at 5:14 pm
-----------
1096896
(1 row(s) affected)
-----------
1096910
(1 row(s) affected)
-----------
1096935
(1 row(s) affected)
-----------
1096935
(1 row(s) affected)
October 19, 2009 at 6:34 am
I noticed when I display the data to a text box in SSRS 2008, it follows the date with a small filled in rectangular character.
However, this data being displayed is the result of a MIN and MAX statement on the counter's date and time fields.
So the MIN and MAX function work just fine which is odd to me that those are able to work normally.
If I try to save the output as .csv and open it in TextPad and then go to copy and paste the character it tells me:
"Cannot cut, copy, or drag and drop text containing null (code = o) characters."
October 19, 2009 at 7:21 am
Maxer (10/16/2009)
Why does this fail?I must be having a slow day today, because I am not seeing my error here...
FAILS:
SELECT CounterDateTime, CONVERT(DATETIME,[CounterData].[CounterDateTime], 121)
FROM [Counterdata]
WORKS:
SELECT CONVERT(DATETIME, '2009-10-15 22:24:40.465', 121)
What am I doing wrong here???
Can someone lend me a hand, or a firm swift kick in the head, because I must be missing something obvious here?
I'm coming in late to this thread so maybe I'm missing something. What is the original data type for the column? Have you tried:
SELECT CAST(CONVERT(VARCHAR(23),CounterDateTime,121) AS DATETIME)
October 19, 2009 at 7:36 am
It is a char(24) field.
What I am trying to do is get the MAX datetime and then do a datediff to see if the MAX counterDateTime is > than GetDate() minus 5 minutes.
If I run:
SELECT CAST(CONVERT(VARCHAR(23),CounterDateTime,121) AS DATETIME)
IT WORKS!
So I'll mess with that a bit, thanks!
Also, I need to lookup WHY it worked... why is cast helping?
Because you first used CONVERT to send it into a character format, and setting the limit of 23 chars, caused that 24th char to be truncated?
So that removed the offending ASCI command code data?
THEN, taking the newly sanitized varchar(23), you cast that to the datetime format, and since those nasty characters were newly removed, it worked just fine?
BRILLIANT!
So the only real penalty here is I'm calling two functions, so technically twice the overhead, but again... not a massive issue.
October 20, 2009 at 7:14 pm
I converted to varchar(23) becuase I counted 23 characters in your datetime value example (date + time). If you just want the date you can strip out the time by using varchar(10) instead.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply