August 10, 2018 at 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?
August 10, 2018 at 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
August 10, 2018 at 9:26 am
Lynn Pettis - Friday, August 10, 2018 8:41 AMCould 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
August 10, 2018 at 9:26 am
dfd.francisco 53179 - Friday, August 10, 2018 9:23 AMHiI 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.
August 10, 2018 at 9:31 am
Lynn Pettis - Friday, August 10, 2018 9:26 AMdfd.francisco 53179 - Friday, August 10, 2018 9:23 AMHiI 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
...
DanielAnd 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. 😎🙂
Regards
...
Daniel
August 10, 2018 at 9:31 am
dfd.francisco 53179 - Friday, August 10, 2018 9:23 AMHiI 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
Given the format your dates are in, you can still do comparisons - you just can't do date arithmetic
WHERE counterdate >= '2018-07-01 00:00:00.000'
AND counterdate < '2018-08-01 00:00:00.000'
But Lynn is right - fix the cause and move on.
John
August 10, 2018 at 9:37 am
I've had to query the CounterData table before.
I use LEFT(CounterDateTime, 23) and it works ok.
I think the 24th character is something like a file terminator.
August 10, 2018 at 9:50 am
Hi John
Yes, i can made the comparisons but the problem is to delete oldest data.
The method i used is not the best but works. This is one isolated situation, because it doesn´t makes sense to have a date column in CHAR or like a string. This is a particular situation because Performance Monitor creates the column this way instead of DATETIME type what its not understandable.
If was a process created by me, surely i create a date column in DATETIME format 🙂😎
Even Microsoft fails in some approachs, like this one
Cheers
...
Daniel
August 10, 2018 at 10:49 am
dfd.francisco 53179 - Friday, August 10, 2018 9:50 AMHi JohnYes, i can made the comparisons but the problem is to delete oldest data.
The method i used is not the best but works. This is one isolated situation, because it doesn´t makes sense to have a date column in CHAR or like a string. This is a particular situation because Performance Monitor creates the column this way instead of DATETIME type what its not understandable.
If was a process created by me, surely i create a date column in DATETIME format 🙂😎
Even Microsoft fails in some approachs, like this one
Cheers
...
Daniel
A guess here would be that originally this information was written to a text file.
Also, no one ever said Microsoft was perfect. Just look at the job history table and how dates and times are stored.
August 11, 2018 at 5:07 pm
This is nuts and it's all because the wrong data type has been used for the column. Petition management to have it changed to a correct, self-validating temporal datatype and all these problems will go away. At the very least, petition them to add a constraint on the table to validate the data as a real date.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 11, 2018 at 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.
August 11, 2018 at 7:37 pm
Jonathan AC Roberts - Saturday, August 11, 2018 5:41 PMI don't think anyone has noticed/seen my solution above. You just need to useLEFT(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.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 12, 2018 at 2:57 am
Jeff Moden - Saturday, August 11, 2018 7:37 PMJonathan AC Roberts - Saturday, August 11, 2018 5:41 PMI don't think anyone has noticed/seen my solution above. You just need to useLEFT(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).
August 12, 2018 at 3:53 pm
Jonathan AC Roberts - Sunday, August 12, 2018 2:57 AMJeff Moden - Saturday, August 11, 2018 7:37 PMJonathan AC Roberts - Saturday, August 11, 2018 5:41 PMI don't think anyone has noticed/seen my solution above. You just need to useLEFT(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 ')
;
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2018 at 3:34 am
Jeff Moden - Sunday, August 12, 2018 3:53 PMJonathan AC Roberts - Sunday, August 12, 2018 2:57 AMJeff Moden - Saturday, August 11, 2018 7:37 PMJonathan AC Roberts - Saturday, August 11, 2018 5:41 PMI don't think anyone has noticed/seen my solution above. You just need to useLEFT(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.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply