August 9, 2018 at 7:32 am
Hello
Acttually i have a regular MSFT performance monitor counter running, which counters are being collected into a SQL Server table.
Because the table as a lot of records, i want to purge data older than 10 days.
When i try to purge data with the query:
delete from [dbo].[CounterData] WHERE counterdatetime < DATEADD(day, -10, GETDATE())
I get the following error:
Conversion failed when converting date and/or time from character string.
I noted the column type is CHAR (24) instead of DATETIME and for that the query blows.
I have searched on the Internet and try resolve, but without success.
Someone as IDEAS to solve this situation? I would appreciate a lot.
Best Regards
Daniel
August 9, 2018 at 7:44 am
What format is the character date?
August 9, 2018 at 7:59 am
Hello Laurie
Thanks for the reply 🙂
The date appears this way when i made a select:
CounterDateTime
2018-08-09 13:34:47.626
The column is (CHAR(24), not null)
...
Daniel
August 9, 2018 at 8:49 am
I think that message means that you have got a value that's not a date somewhere in the column.
It's not a blank or an invalid date.
August 9, 2018 at 9:00 am
SELECT counterdatetime
FROM CounterData
WHERE TRY_CONVERT(datetime,counterdatetime) IS NULL
John
August 9, 2018 at 9:16 am
Hi John Thank you
Now something is missing me.
When i run this:
delete from [dbo].[CounterData]
where counterdatetime IN
(
SELECT counterdatetime
FROM CounterData
WHERE TRY_CONVERT(datetime,counterdatetime) IS NULL)
It delete´s all data from the table.
Now, how can i keep data from today and delete previous days data (purge)?
If someone can help me, i appreciate so much
...
Daniel
August 9, 2018 at 9:24 am
Daniel
Well, if you deleted it all, then you're going to struggle to keep today's data! Seriously, though - if you deleted all data then it must mean that all the values in counterdatetime must have been invalid. Please will you post DDL (CREATE TABLE statement) and some sample data (INSERT statement(s)) so that we can see what sort of data you're looking at?
John
August 9, 2018 at 9:31 am
dfd.francisco 53179 - Thursday, August 9, 2018 9:16 AMHi John Thank youNow something is missing me.
When i run this:
delete from [dbo].[CounterData]
where counterdatetime IN
(
SELECT counterdatetime
FROM CounterData
WHERE TRY_CONVERT(datetime,counterdatetime) IS NULL)
It delete´s all data from the table.Now, how can i keep data from today and delete previous days data (purge)?
If someone can help me, i appreciate so much
...
Daniel
Hmm... wouldn't that mean none of the values in your table are actually in the format you provided earlier?
SELECT TRY_CONVERT(datetime, '2018-08-09 13:34:47.626')
This returns a value for me not a null so based on that delete statement it wouldn't have been deleted if it was actually a value?
August 9, 2018 at 9:33 am
dfd.francisco 53179 - Thursday, August 9, 2018 9:16 AMHi John Thank youNow something is missing me.
When i run this:
delete from [dbo].[CounterData]
where counterdatetime IN
(
SELECT counterdatetime
FROM CounterData
WHERE TRY_CONVERT(datetime,counterdatetime) IS NULL)
It delete´s all data from the table.Now, how can i keep data from today and delete previous days data (purge)?
If someone can help me, i appreciate so much
...
Daniel
You do realize that John just wanted you to run the following, right?
SELECT counterdatetime
FROM CounterData
WHERE TRY_CONVERT(datetime,counterdatetime) IS NULL
August 9, 2018 at 9:44 am
Hi John
When i run the statement
SELECT counterdatetime
FROM CounterData
WHERE TRY_CONVERT(datetime,counterdatetime) IS NULL
it give me:
counterdatetime
2018-08-09 16:07:07.038
2018-08-09 16:07:11.931
2018-08-09 16:07:16.934
I try to explain from the beggining.
I have created a performance counter collector in a SQL server, where the information is being collected into a database that i have aleady creasted. the Perfmon tool, has created 4 tables when i change the collector log format into SQL:
-CounterCollections
-CounterData
-CounterDetails
-DisplayToID
When i start the collector, the tables are filled with perf counters information. The "counterdata" is the table where the collected information remains. Because i have a lot of counters, the table growths very fast (100 MB/day). Thats the reason why i want to create a statement, which allows me to delete records older than 10 days.
The "counterdata" table has a column which by default is CHAR(24) instead off DATETIME:
this is how the permon creates the table
CREATE TABLE [dbo].[CounterData](
[GUID] [uniqueidentifier] NOT NULL,
[CounterID] [int] NOT NULL,
[RecordIndex] [int] NOT NULL,
[CounterDateTime] [char](24) NOT NULL,
[CounterValue] [float] NOT NULL,
[FirstValueA] [int] NULL,
[FirstValueB] [int] NULL,
[SecondValueA] [int] NULL,
[SecondValueB] [int] NULL,
[MultiCount] [int] NULL,
To delete all data except the last 10 days, i tryed to run the following query:
delete from [dbo].[CounterData] WHERE counterdatetime < DATEADD(day, -10, GETDATE())
Bbut whithout success because the column counterdatetime is a string instead of DATETIME.
Conversion failed when converting date and/or time from character string.
The table has the content type:
GUID CounterID RecordIndex CounterDateTime CounterValue FirstValueA FirstValueB SecondValueA SecondValueB MultiCount
A3C154C5-A4C5-4EAE-BDCA-80237E4EAF49 1 1 2018-08-08 00:00:01.047 3138 3138 0 0 0 1
There is no problem on this moment for the deleted table, but i want to assure that i have a purge mechanism when i put the performance collectors working "Officially".
Thanks a lot for the atention
Regards
Daniel
August 9, 2018 at 9:46 am
Lynn Pettis - Thursday, August 9, 2018 9:33 AMdfd.francisco 53179 - Thursday, August 9, 2018 9:16 AMHi John Thank youNow something is missing me.
When i run this:
delete from [dbo].[CounterData]
where counterdatetime IN
(
SELECT counterdatetime
FROM CounterData
WHERE TRY_CONVERT(datetime,counterdatetime) IS NULL)
It delete´s all data from the table.Now, how can i keep data from today and delete previous days data (purge)?
If someone can help me, i appreciate so much
...
DanielYou do realize that John just wanted you to run the following, right?
SELECT counterdatetime
FROM CounterData
WHERE TRY_CONVERT(datetime,counterdatetime) IS NULL
Hi Lynn
Yes 🙂 no problem, because i am testing this solution for now 🙂
August 9, 2018 at 10:08 am
Is this a locale issue? What country are you in?
August 10, 2018 at 2:05 am
laurie-789651 - Thursday, August 9, 2018 10:08 AMIs this a locale issue? What country are you in?
Hi, It´s not a local issue. It´s a Convertion problem because of the column type.
Hi try a workarround, deleting and recreating the table with the column in DATETIME, but then the performance counter BLOWS :pinch:
August 10, 2018 at 2:21 am
dfd.francisco 53179 - Friday, August 10, 2018 2:05 AMHi try a workarround, deleting and recreating the table with the column in DATETIME, but then the performance counter BLOWS :pinch:
What do you mean by that? If you have the option to redesign the table then that's what you should do. Store dates as dates. Even if performance is worse, it's still working as it should. If you can't change the table, then try taking the hyphens out of the stored dates. I couldn't replicate that myself, but I think I've heard reports that sometimes the hyphen stops the date from being convertible.
SELECT CAST('2018-08-10 09:16:57.803' AS datetime)
SELECT CAST('20180810 09:16:57.803' AS datetime)
John
August 10, 2018 at 6:51 am
John Mitchell-245523 - Friday, August 10, 2018 2:21 AMdfd.francisco 53179 - Friday, August 10, 2018 2:05 AMHi try a workarround, deleting and recreating the table with the column in DATETIME, but then the performance counter BLOWS :pinch:What do you mean by that? If you have the option to redesign the table then that's what you should do. Store dates as dates. Even if performance is worse, it's still working as it should. If you can't change the table, then try taking the hyphens out of the stored dates. I couldn't replicate that myself, but I think I've heard reports that sometimes the hyphen stops the date from being convertible.
SELECT CAST('2018-08-10 09:16:57.803' AS datetime)
SELECT CAST('20180810 09:16:57.803' AS datetime)John
I John
I cannot redesign the table with the column with the right format (DATETIME) because it´s a default table for Perfomon. I have tryed and recreated the table but when i start the performance monitor counters , the process blows up.
I am out of ideas, I only want to purge data oldest than 10 days, ARGHHHH :pinch:
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply