August 6, 2012 at 1:12 am
Hello everyone,
First of all, I hate working with dates, hahaha, hum... I checked all the topics related to my "problem" but none of the solutions given work :/
I need to get all the records older than 5 hours in my table. My column containing the dates is of type CHAR so my idea was to write my query like this:
SELECT count(1)
FROM [MSSQL_PerfmonCollector].[dbo].[CounterData]
WHERE CONVERT(datetime,CounterDateTime) < DATEADD(hour,-5,GETDATE());
Unfortunately I get this well known error:
Conversion failed when converting date and/or time from character string.
As far as I know, DATEADD and GETDATE() both return a DATETIME, so I think that, to be able to use my where clause, I have to convert my CounterDateTime column into a DATETIME. Am I wrong ?
But, shame on me, it doesn't work... What/How can I do ?
PS: Here's an example of the data contained in my column: '2012-07-22 12:30:12.784'
Thanks in advance.
Regards.
Franck.
August 6, 2012 at 1:27 am
I'm guessing that when you run this: -
SELECT CONVERT(datetime,CounterDateTime)
FROM [MSSQL_PerfmonCollector].[dbo].[CounterData];
You have the same error?
If so, you have some bad data in there, which is why you should always be using the correct datatype.
You could try something like this: -
SELECT COUNT(1)
FROM (SELECT CounterDateTime
FROM [MSSQL_PerfmonCollector].[dbo].[CounterData]
WHERE CounterDateTime LIKE '[1-2][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9] [0-2][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
)a
WHERE CONVERT(datetime,a.CounterDateTime) < DATEADD(hour,-5,GETDATE());
But you'll be stripping out some of your data.
This will identify how many I've identified as "bad": -
SELECT
SUM(CASE WHEN CounterDateTime LIKE '[1-2][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9] [0-2][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
THEN 1 ELSE 0 END) dateFormats,
SUM(CASE WHEN CounterDateTime LIKE '[1-2][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9] [0-2][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
THEN 0 ELSE 1 END) notDateFormats,
FROM [MSSQL_PerfmonCollector].[dbo].[CounterData];
August 6, 2012 at 1:35 am
Thanks for fast reply 🙂
You are guessing well, the first query doesn't work, the second either by the way :/
The third one tells me that approximatively all my records are not of type DATE (they are of type CHAR(24)). And 0 are dateformats.
Franck.
August 6, 2012 at 1:38 am
set dateformat MDY
go
select CONVERT(datetime,'2012-07-22 12:30:12.784')
set dateformat DMY
go
select CONVERT(datetime,'2012-07-22 12:30:12.784')
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 6, 2012 at 1:42 am
franck.maton (8/6/2012)
Thanks for fast reply 🙂You are guessing well, the first query doesn't work, the second either by the way :/
The third one tells me that approximatively all my records are not of type DATE (they are of type CHAR(24)). And 0 are dateformats.
Franck.
Sorry, back up a second, did you say CHAR(24)?
A datetime is 23 characters long, so try this instead: -
SELECT COUNT(1)
FROM (SELECT RTRIM(CounterDateTime)
FROM [MSSQL_PerfmonCollector].[dbo].[CounterData]
WHERE RTRIM(CounterDateTime) LIKE '[1-2][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9] [0-2][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
)a(CounterDateTime)
WHERE CONVERT(datetime,a.CounterDateTime) < DATEADD(hour,-5,GETDATE());
To have a look at missing data, change the third query to this: -
SELECT
SUM(CASE WHEN RTRIM(CounterDateTime) LIKE '[1-2][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9] [0-2][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
THEN 1 ELSE 0 END) dateFormats,
SUM(CASE WHEN RTRIM(CounterDateTime) LIKE '[1-2][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9] [0-2][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
THEN 0 ELSE 1 END) notDateFormats,
FROM [MSSQL_PerfmonCollector].[dbo].[CounterData];
August 6, 2012 at 1:57 am
@j-2 LivingStone SQL
The 1st query you posted works but the second doesn't. If I try to replace the date in your 1st query by my CounterDateTime column, it doesn't works anymore.
@Cadavres
I didn't know that a datetime was 23 character long...
The 1st query you posted still gives me the same error:
Conversion failed when converting date and/or time from character string.
And the second one gives approx. the same result as the one you posted in your first reply to the topic.
I'm disappointed 🙁
Franck.
August 6, 2012 at 1:59 am
For retrieving all records of before 5 hours, you can write your query as:
SELECT *
FROM [MSSQL_PerfmonCollector].[dbo].[CounterData] (NOLOCK) T
WHERE Convert(Datetime, T.CounterDateTime) < DATEADD(HH,-5,GETDATE())
Thanks,
Sumit 🙂
August 6, 2012 at 3:00 am
I asked my boss if I could get rid of the milliseconds and it seems I can. So If I write the query like this:
SELECT count(1)
FROM [MSSQL_PerfmonCollector].[dbo].[CounterData]
WHERE CONVERT(datetime,SUBSTRING(CounterDateTime,0,20)) < DATEADD(hour,-5,GETDATE())
It works.
I still don't know why it doesn't work if I take the full column, but If I stop at the seconds, it's ok.
Thanks for your help anyway 🙂
Regards.
Franck.
August 6, 2012 at 3:28 am
franck.maton (8/6/2012)
I asked my boss if I could get rid of the milliseconds and it seems I can. So If I write the query like this:
SELECT count(1)
FROM [MSSQL_PerfmonCollector].[dbo].[CounterData]
WHERE CONVERT(datetime,SUBSTRING(CounterDateTime,0,20)) < DATEADD(hour,-5,GETDATE())
It works.
I still don't know why it doesn't work if I take the full column, but If I stop at the seconds, it's ok.
Thanks for your help anyway 🙂
Regards.
Franck.
While you're at it, it might be best to create a VIEW where the CounterDateTime is cast as a DATETIME.
August 6, 2012 at 3:32 am
Can you provide the CounterDateTime values
SELECT CounterDateTime
FROM [MSSQL_PerfmonCollector].[dbo].[CounterData]
WHERE CounterDateTime LIKE '2012-08-06%'
Assuming you use ISO formatting, other wise change the like to match your character date storage.
August 6, 2012 at 3:38 am
@anthony.Green
I could, but I've more than 3.600.000 records for today. But it's like:
2012-08-06 00:05:28.819
2012-08-06 00:05:28.819
2012-08-06 00:05:28.819
2012-08-06 00:05:28.819
2012-08-06 00:05:28.819
2012-08-06 00:05:28.819
2012-08-06 00:05:28.819
2012-08-06 00:05:28.820
What are you thinking of ?
Yep, i'm working on it 🙂
Thanks to all for your replies.
Franck.
August 6, 2012 at 3:49 am
franck.maton (8/6/2012)
@Anthony.GreenI could, but I've more than 3.600.000 records for today. But it's like:
2012-08-06 00:05:28.819
2012-08-06 00:05:28.819
2012-08-06 00:05:28.819
2012-08-06 00:05:28.819
2012-08-06 00:05:28.819
2012-08-06 00:05:28.819
2012-08-06 00:05:28.819
2012-08-06 00:05:28.820
What are you thinking of ?
Yep, i'm working on it 🙂
Thanks to all for your replies.
Franck.
Trying to identify why you cannot convert to datetime.
select counterdatetime
from [MSSQL_PerfmonCollector].[dbo].[CounterData]
where ISDATE(counterdatetime) = 0
All the dates provided will convert, but will round up to .820 due to the scale of DATETIME, the above query should hopefully identify the dodgy row(s).
August 6, 2012 at 4:09 am
Okay, the result is: All my rows 🙂
Franck.
August 6, 2012 at 4:12 am
does
2012-07-29 00:05:28.819
work for you?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply