Performance Counter Data

I have a couple performance counters logging data to a sql 2000 database.
The date column has a data type of varchar and I am not able to perform any
date functions against this column in my sql queries. I am trying to use th
e
DATEADD function and the BOL reference states that character data can be use
d
in the DATEADD function if it is in a date format. The format of the date i
s
2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
function, I receive an error: "error converting datetime from character
string". Does anyone know what might be causing this or how I can get aroun
d
it? I have no control over the setup of the table that holds the performanc
e
counter data. It is created automatically when the counter is set to log to
a database. Thanks for any help!phavel,
i created a table with one field varchar 50.
created a view doing select dateadd(day,1,myfield) and it worked fine.
can you elaborate on your code and your schema?
Mike
www.michaelevanchik.com
"phavel" wrote:

> I have a couple performance counters logging data to a sql 2000 database.
> The date column has a data type of varchar and I am not able to perform an
y
> date functions against this column in my sql queries. I am trying to use
the
> DATEADD function and the BOL reference states that character data can be u
sed
> in the DATEADD function if it is in a date format. The format of the date
is
> 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEA
DD
> function, I receive an error: "error converting datetime from character
> string". Does anyone know what might be causing this or how I can get aro
und
> it? I have no control over the setup of the table that holds the performa
nce
> counter data. It is created automatically when the counter is set to log
to
> a database. Thanks for any help!|||Thanks for the response. This appears to be something specific to the way
the performance counter logs the data. BOL states that there should be no
problem using date functions against columns of character data, and your tes
t
supports that. I used query analyzer to return a small amount of data from
the column in question, and in the results pane it looks correct. However i
f
I copy some of the dates out of query analyzer and paste them into Excel,
they lose the data and only the minutes are pasted. The performance counter
seems to be doing something funny as it logs data, but I'm not sure what or
if there's any way to work around it. The VIEW I'm trying to create is
essentially the same as what you wrote. I get the same error when trying to
run it as a select statement in query analyzer. Thanks for any other ideas!
"Michael Evanchik" wrote:
[vbcol=seagreen]
>
> phavel,
> i created a table with one field varchar 50.
> created a view doing select dateadd(day,1,myfield) and it worked fine.
> can you elaborate on your code and your schema?
> Mike
> www.michaelevanchik.com
> "phavel" wrote:
>|||phavel,
i wouldnt paste into excel if you really want to look at data. try pasting
into notepad or wordpad, no data will be lost that way. Also you can paste
special i think in excel as pure text but im no excel expert. also does you
r
convert work if you specifiy one column in one row ? or does it only error
when you try to convert the whole table of that field?
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
[vbcol=seagreen]
> Thanks for the response. This appears to be something specific to the way
> the performance counter logs the data. BOL states that there should be no
> problem using date functions against columns of character data, and your t
est
> supports that. I used query analyzer to return a small amount of data fro
m
> the column in question, and in the results pane it looks correct. However
if
> I copy some of the dates out of query analyzer and paste them into Excel,
> they lose the data and only the minutes are pasted. The performance count
er
> seems to be doing something funny as it logs data, but I'm not sure what o
r
> if there's any way to work around it. The VIEW I'm trying to create is
> essentially the same as what you wrote. I get the same error when trying
to
> run it as a select statement in query analyzer. Thanks for any other idea
s!
> "Michael Evanchik" wrote:
>|||Yeah, I realized the Excel test was no good after I made my last post. I've
figured this out to a certain extent, but I'm still not sure of a good way
around it. When the date is placed in the table by the performance counter,
it is putting something "extra" at the end of the value. It appears that
this is why I cannot perform date functions. If I copy and paste one of the
dates to a different table that I created, I get the same error. However, i
f
I go to that cell and delete whatever extra is at the end, I can then perfor
m
date functions. It does not appear to simply be a space, because I can add
a
trailing space manually and it still works fine. Any ideas on what can be
done? I guess I can have a separate query that runs periodically to cleanup
the date data, but this could get messy with timing. Any suggestions
welcome. Thanks!
"Michael Evanchik" wrote:
[vbcol=seagreen]
> phavel,
> i wouldnt paste into excel if you really want to look at data. try pastin
g
> into notepad or wordpad, no data will be lost that way. Also you can past
e
> special i think in excel as pure text but im no excel expert. also does y
our
> convert work if you specifiy one column in one row ? or does it only error
> when you try to convert the whole table of that field?
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" wrote:
>|||dateadd(day,1,substring(field,1,12))
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
[vbcol=seagreen]
> Yeah, I realized the Excel test was no good after I made my last post. I'
ve
> figured this out to a certain extent, but I'm still not sure of a good way
> around it. When the date is placed in the table by the performance counte
r,
> it is putting something "extra" at the end of the value. It appears that
> this is why I cannot perform date functions. If I copy and paste one of t
he
> dates to a different table that I created, I get the same error. However,
if
> I go to that cell and delete whatever extra is at the end, I can then perf
orm
> date functions. It does not appear to simply be a space, because I can ad
d a
> trailing space manually and it still works fine. Any ideas on what can be
> done? I guess I can have a separate query that runs periodically to clean
up
> the date data, but this could get messy with timing. Any suggestions
> welcome. Thanks!
> "Michael Evanchik" wrote:
>|||Worked pefect. Thanks a ton for the help.
"Michael Evanchik" wrote:
[vbcol=seagreen]
> dateadd(day,1,substring(field,1,12))
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" wrote:
>|||awesome! click yes this post was helpful to close this thread =)
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
[vbcol=seagreen]
> Worked pefect. Thanks a ton for the help.
> "Michael Evanchik" wrote:
>