SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem Converting DATE. Trying to Purge Data Oldest than 30 days from Performance Counter Table...


Problem Converting DATE. Trying to Purge Data Oldest than 30 days from Performance Counter Table where date column is char (24)

Author
Message
dfd.francisco 53179
dfd.francisco 53179
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 63
Lynn Pettis - Thursday, August 9, 2018 9:33 AM
dfd.francisco 53179 - Thursday, August 9, 2018 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

You 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 Smile no problem, because i am testing this solution for now Smile

laurie-789651
laurie-789651
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6761 Visits: 1936
Is this a locale issue? What country are you in?
dfd.francisco 53179
dfd.francisco 53179
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 63
laurie-789651 - Thursday, August 9, 2018 10:08 AM
Is 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

John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)

Group: General Forum Members
Points: 135306 Visits: 19488
dfd.francisco 53179 - Friday, August 10, 2018 2:05 AM
Hi 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

dfd.francisco 53179
dfd.francisco 53179
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 63
John Mitchell-245523 - Friday, August 10, 2018 2:21 AM
dfd.francisco 53179 - Friday, August 10, 2018 2:05 AM
Hi 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

Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)

Group: General Forum Members
Points: 401253 Visits: 43397
Could you post the complete error message you get regarding the conversion failure? Also, what is the date format on your system, is it mm/dd/yyyy or dd/mm/yyyy?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
dfd.francisco 53179
dfd.francisco 53179
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 63
Hi

I have created a workaround:

...
if month(getdate()) = '9'
delete FROM dbo.CounterData WHERE CounterDateTime like '%-07-%'
go
if month(getdate()) = '10'
delete FROM dbo.CounterData WHERE CounterDateTime like '%-08-%'
go
...


Running this statement for example on 1st day of September, will delete all data from july (month 07). It´s not the best way to do it, but it works. Assuming this runs every month, you guarantee that only one month of data will be available

I put this code (validating 12 months) in a SQL Agent Job running on 1st day of each month.

For now its what i need

Thanks a lot for all sugestions

Regards
...
Daniel
dfd.francisco 53179
dfd.francisco 53179
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 63
Lynn Pettis - Friday, August 10, 2018 8:41 AM
Could you post the complete error message you get regarding the conversion failure? Also, what is the date format on your system, is it mm/dd/yyyy or dd/mm/yyyy?

Hi Lynn

The error:
Conversion failed when converting date and/or time from character string.

The server Date Format:
dd/MM/yyyy

Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)

Group: General Forum Members
Points: 401253 Visits: 43397
dfd.francisco 53179 - Friday, August 10, 2018 9:23 AM
Hi

I have created a workaround:

...
if month(getdate()) = '9'
delete FROM dbo.CounterData WHERE CounterDateTime like '%-07-%'
go
if month(getdate()) = '10'
delete FROM dbo.CounterData WHERE CounterDateTime like '%-08-%'
go
...


Running this statement for example on 1st day of September, will delete all data from july (month 07). It´s not the best way to do it, but it works. Assuming this runs every month, you guarantee that only one month of data will be available

I put this code (validating 12 months) in a SQL Agent Job running on 1st day of each month.

For now its what i need

Thanks a lot for all sugestions

Regards
...
Daniel

And you will have to do this every month. You need to solve the problem so that you may go on to other things.


Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
dfd.francisco 53179
dfd.francisco 53179
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 63
Lynn Pettis - Friday, August 10, 2018 9:26 AM
dfd.francisco 53179 - Friday, August 10, 2018 9:23 AM
Hi

I have created a workaround:

...
if month(getdate()) = '9'
delete FROM dbo.CounterData WHERE CounterDateTime like '%-07-%'
go
if month(getdate()) = '10'
delete FROM dbo.CounterData WHERE CounterDateTime like '%-08-%'
go
...


Running this statement for example on 1st day of September, will delete all data from july (month 07). It´s not the best way to do it, but it works. Assuming this runs every month, you guarantee that only one month of data will be available

I put this code (validating 12 months) in a SQL Agent Job running on 1st day of each month.

For now its what i need

Thanks a lot for all sugestions

Regards
...
Daniel

And you will have to do this every month. You need to solve the problem so that you may go on to other things.

Since the table is empty, for example, in 1st of October, the August Data will be deleted by the job. That job will run every month and validate the month using the function month(getdate()) = ''. This way i assure that the table will only have 1 month of information.

It´s not an ideal scenario, but it fit´s for what i want. CoolSmile

Regards

...
Daniel



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search