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

Question

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

Viewing 2 posts - 31 through 31 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

Jonathan AC Roberts - Monday, August 13, 2018 3:34 AM
Jeff Moden - Sunday, August 12, 2018 3:53 PM
Jonathan AC Roberts - Sunday, August 12, 2018 2:57 AM
Jeff Moden - Saturday, August 11, 2018 7:37 PM
Jonathan AC Roberts - Saturday, August 11, 2018 5:41 PM
I don't think anyone has noticed/seen my solution above. You just need to use LEFT(CounterDateTime, 23) and it will work!
So Daniel can just use:
delete from [dbo].[CounterData] WHERE LEFT(CounterDateTime, 23) < DATEADD(day, -10, GETDATE()) to get it to work.
I'm a bit paranoid about relying on default formatting. It doesn't cost that much more to force a given format. The calculation on the column in the WHERE clause presents a possible performance problem, as well.
Again and still... you wouldn't need to do any of this if the column were of a correct temporal data type. Even if you couldn't change the column, a persisted/index computed column would likely make this sail.
I've not sure why Microsoft made it write to the database like this. But PerfMon will insert the data onto multiple sources: any database using an ODBC data source, there is also the choice of writing csv files, tab separated files and files with Perfmon's own binary format. So that might be a bit of the reason why the date is a bit odd. The format of the date is 'yyyy-MM-dd hh:mm:ss.000' which SQL server can implicitly convert to datetime.
But the thing that has stopped Daniel's query from working is the 24th character of this column (the string 'yyyy-MM-dd hh:mm:ss.000' is only 23 characters long).
Another good reason to not store (except in an import staging table) dates and times as character based data. I wonder what the abhorrent 24th character actually is because a space in that position causes no problems.
SELECT CONVERT(DATETIME,'2018-08-12 17:49:35.123') ,CONVERT(DATETIME,'2018-08-12 17:49:35.123 ') ;
From the following query I obtained its ASCII value to be zero.
select CounterDateTime,ASCII(SUBSTRING(CounterDateTime,24,1)) from [dbo].[CounterData]
So trying to convert to datetime is the same as running this statement:
SELECT CONVERT(DATETIME,'2018-08-12 17:49:35.123' + CHAR(0))
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Ugh... yeah... not possible because CHAR(0) is an ASCII "NULL" Character. You've just got to wonder how data gets into such a condition.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dfd.francisco 53179 SSC Veteran Points: 231 More actions · Answer 2

Jeff Moden - Monday, August 13, 2018 8:22 PM
Jonathan AC Roberts - Monday, August 13, 2018 3:34 AM
Jeff Moden - Sunday, August 12, 2018 3:53 PM
Jonathan AC Roberts - Sunday, August 12, 2018 2:57 AM
Jeff Moden - Saturday, August 11, 2018 7:37 PM
Jonathan AC Roberts - Saturday, August 11, 2018 5:41 PM
I don't think anyone has noticed/seen my solution above. You just need to use LEFT(CounterDateTime, 23) and it will work!
So Daniel can just use:
delete from [dbo].[CounterData] WHERE LEFT(CounterDateTime, 23) < DATEADD(day, -10, GETDATE()) to get it to work.
I'm a bit paranoid about relying on default formatting. It doesn't cost that much more to force a given format. The calculation on the column in the WHERE clause presents a possible performance problem, as well.
Again and still... you wouldn't need to do any of this if the column were of a correct temporal data type. Even if you couldn't change the column, a persisted/index computed column would likely make this sail.
I've not sure why Microsoft made it write to the database like this. But PerfMon will insert the data onto multiple sources: any database using an ODBC data source, there is also the choice of writing csv files, tab separated files and files with Perfmon's own binary format. So that might be a bit of the reason why the date is a bit odd. The format of the date is 'yyyy-MM-dd hh:mm:ss.000' which SQL server can implicitly convert to datetime.
But the thing that has stopped Daniel's query from working is the 24th character of this column (the string 'yyyy-MM-dd hh:mm:ss.000' is only 23 characters long).
Another good reason to not store (except in an import staging table) dates and times as character based data. I wonder what the abhorrent 24th character actually is because a space in that position causes no problems.
SELECT CONVERT(DATETIME,'2018-08-12 17:49:35.123') ,CONVERT(DATETIME,'2018-08-12 17:49:35.123 ') ;
From the following query I obtained its ASCII value to be zero.
select CounterDateTime,ASCII(SUBSTRING(CounterDateTime,24,1)) from [dbo].[CounterData]
So trying to convert to datetime is the same as running this statement:
SELECT CONVERT(DATETIME,'2018-08-12 17:49:35.123' + CHAR(0))
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Ugh... yeah... not possible because CHAR(0) is an ASCII "NULL" Character. You've just got to wonder how data gets into such a condition.

Hi everybody

I have used "Jonathan AC Roberts" code and it works fine :).

In terms of performance i donÂ´t identify any problem yet. I am collecting about 100K ROWS in a day and will only have at maximum 1Milion of ROWS (150MB)

ItÂ´s ridiculous for Microsoft to create a date time field like a STRING. Date and Time columns should only be DATETIME type. Now in CHAR 24? this type of format is static, ItÂ´s useless.

Thanks Jonathan