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 the
DATEADD function and the BOL reference states that character data can be used
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 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 around
it? I have no control over the setup of the table that holds the performance
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 any
> 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 used
> 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 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 around
> it? I have no control over the setup of the table that holds the performance
> 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 test
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 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 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 your
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 test
> 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 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 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:
|||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, if
I go to that cell and delete whatever extra is at the end, I can then perform
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 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 your
> 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 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, if
> I go to that cell and delete whatever extra is at the end, I can then perform
> 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:
|||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:
sql
Showing posts with label date. Show all posts
Showing posts with label date. Show all posts
Friday, March 23, 2012
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:
>
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:
>
Wednesday, March 21, 2012
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 the
DATEADD function and the BOL reference states that character data can be used
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 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 around
it? I have no control over the setup of the table that holds the performance
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 any
> 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 used
> 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 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 around
> it? I have no control over the setup of the table that holds the performance
> 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 test
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 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 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:
>
> 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 any
> > 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 used
> > 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 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 around
> > it? I have no control over the setup of the table that holds the performance
> > counter data. It is created automatically when the counter is set to log to
> > a database. Thanks for any help!|||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 your
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:
> 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 test
> 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 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 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:
> >
> >
> > 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 any
> > > 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 used
> > > 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 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 around
> > > it? I have no control over the setup of the table that holds the performance
> > > counter data. It is created automatically when the counter is set to log to
> > > a database. Thanks for any help!|||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, if
I go to that cell and delete whatever extra is at the end, I can then perform
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:
> 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 your
> 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:
> > 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 test
> > 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 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 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:
> >
> > >
> > >
> > > 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 any
> > > > 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 used
> > > > 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 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 around
> > > > it? I have no control over the setup of the table that holds the performance
> > > > counter data. It is created automatically when the counter is set to log to
> > > > a database. Thanks for any help!|||dateadd(day,1,substring(field,1,12))
Michael Evanchik
www.michaelevanchik.com
"phavel" 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, if
> I go to that cell and delete whatever extra is at the end, I can then perform
> 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:
> > 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 your
> > 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:
> >
> > > 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 test
> > > 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 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 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:
> > >
> > > >
> > > >
> > > > 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 any
> > > > > 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 used
> > > > > 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 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 around
> > > > > it? I have no control over the setup of the table that holds the performance
> > > > > counter data. It is created automatically when the counter is set to log to
> > > > > a database. Thanks for any help!|||Worked pefect. Thanks a ton for the help.
"Michael Evanchik" wrote:
> dateadd(day,1,substring(field,1,12))
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" 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, if
> > I go to that cell and delete whatever extra is at the end, I can then perform
> > 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:
> >
> > > 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 your
> > > 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:
> > >
> > > > 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 test
> > > > 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 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 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:
> > > >
> > > > >
> > > > >
> > > > > 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 any
> > > > > > 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 used
> > > > > > 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 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 around
> > > > > > it? I have no control over the setup of the table that holds the performance
> > > > > > counter data. It is created automatically when the counter is set to log to
> > > > > > a database. Thanks for any help!|||awesome! click yes this post was helpful to close this thread =)
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
> Worked pefect. Thanks a ton for the help.
> "Michael Evanchik" wrote:
> > dateadd(day,1,substring(field,1,12))
> >
> > Michael Evanchik
> >
> > www.michaelevanchik.com
> >
> > "phavel" 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, if
> > > I go to that cell and delete whatever extra is at the end, I can then perform
> > > 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:
> > >
> > > > 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 your
> > > > 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:
> > > >
> > > > > 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 test
> > > > > 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 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 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:
> > > > >
> > > > > >
> > > > > >
> > > > > > 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 any
> > > > > > > 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 used
> > > > > > > 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 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 around
> > > > > > > it? I have no control over the setup of the table that holds the performance
> > > > > > > counter data. It is created automatically when the counter is set to log to
> > > > > > > a database. Thanks for any help!
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 the
DATEADD function and the BOL reference states that character data can be used
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 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 around
it? I have no control over the setup of the table that holds the performance
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 any
> 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 used
> 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 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 around
> it? I have no control over the setup of the table that holds the performance
> 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 test
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 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 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:
>
> 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 any
> > 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 used
> > 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 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 around
> > it? I have no control over the setup of the table that holds the performance
> > counter data. It is created automatically when the counter is set to log to
> > a database. Thanks for any help!|||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 your
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:
> 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 test
> 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 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 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:
> >
> >
> > 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 any
> > > 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 used
> > > 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 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 around
> > > it? I have no control over the setup of the table that holds the performance
> > > counter data. It is created automatically when the counter is set to log to
> > > a database. Thanks for any help!|||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, if
I go to that cell and delete whatever extra is at the end, I can then perform
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:
> 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 your
> 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:
> > 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 test
> > 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 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 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:
> >
> > >
> > >
> > > 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 any
> > > > 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 used
> > > > 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 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 around
> > > > it? I have no control over the setup of the table that holds the performance
> > > > counter data. It is created automatically when the counter is set to log to
> > > > a database. Thanks for any help!|||dateadd(day,1,substring(field,1,12))
Michael Evanchik
www.michaelevanchik.com
"phavel" 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, if
> I go to that cell and delete whatever extra is at the end, I can then perform
> 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:
> > 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 your
> > 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:
> >
> > > 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 test
> > > 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 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 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:
> > >
> > > >
> > > >
> > > > 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 any
> > > > > 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 used
> > > > > 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 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 around
> > > > > it? I have no control over the setup of the table that holds the performance
> > > > > counter data. It is created automatically when the counter is set to log to
> > > > > a database. Thanks for any help!|||Worked pefect. Thanks a ton for the help.
"Michael Evanchik" wrote:
> dateadd(day,1,substring(field,1,12))
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" 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, if
> > I go to that cell and delete whatever extra is at the end, I can then perform
> > 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:
> >
> > > 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 your
> > > 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:
> > >
> > > > 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 test
> > > > 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 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 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:
> > > >
> > > > >
> > > > >
> > > > > 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 any
> > > > > > 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 used
> > > > > > 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 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 around
> > > > > > it? I have no control over the setup of the table that holds the performance
> > > > > > counter data. It is created automatically when the counter is set to log to
> > > > > > a database. Thanks for any help!|||awesome! click yes this post was helpful to close this thread =)
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
> Worked pefect. Thanks a ton for the help.
> "Michael Evanchik" wrote:
> > dateadd(day,1,substring(field,1,12))
> >
> > Michael Evanchik
> >
> > www.michaelevanchik.com
> >
> > "phavel" 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, if
> > > I go to that cell and delete whatever extra is at the end, I can then perform
> > > 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:
> > >
> > > > 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 your
> > > > 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:
> > > >
> > > > > 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 test
> > > > > 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 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 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:
> > > > >
> > > > > >
> > > > > >
> > > > > > 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 any
> > > > > > > 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 used
> > > > > > > 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 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 around
> > > > > > > it? I have no control over the setup of the table that holds the performance
> > > > > > > counter data. It is created automatically when the counter is set to log to
> > > > > > > a database. Thanks for any help!
Friday, March 9, 2012
Performance - php scripts
A vendor wrote a php script to manipulate date within SQL Server, basically
process data from 1 table and copy the data to multiple tables within the
same server. He said the preformance for others client run really fast,
except for ours. Our process run about 1 record (40 fields) per second. fo
r
table with 200K records, processing time is more than 8 hours, this is
unexceptable for our environment.
I am trying to figure out what I need to do within SQL server to speed up
the process.
Truncate log or Transactions?
Any help is greatly appreciate?
Thanks,
Culam8 Hours for 200K rows. That must be some really poor script. I don't know
what it is doing but it should only take a few minutes if done correctly.
Without seeinghte code it is pretty hard to say. One thing that when you are
doing a lot of data manipulation you should ensure you have plenty of room
in the log file and it should be placed on a different drive array than the
data file. Preferably a Raid 1.
Andrew J. Kelly SQL MVP
"culam" <culam@.discussions.microsoft.com> wrote in message
news:465DB2E9-14AA-4AE2-B83F-A79C73458BD3@.microsoft.com...
>A vendor wrote a php script to manipulate date within SQL Server, basically
> process data from 1 table and copy the data to multiple tables within the
> same server. He said the preformance for others client run really fast,
> except for ours. Our process run about 1 record (40 fields) per second.
> for
> table with 200K records, processing time is more than 8 hours, this is
> unexceptable for our environment.
> I am trying to figure out what I need to do within SQL server to speed up
> the process.
> Truncate log or Transactions?
> Any help is greatly appreciate?
> Thanks,
> Culam
>|||Unfortunately, we don't have access to the codes and the Client insists ther
e
codes is not the problem.
How can I verify if log file is the problem?
Culam
"Andrew J. Kelly" wrote:
> 8 Hours for 200K rows. That must be some really poor script. I don't kno
w
> what it is doing but it should only take a few minutes if done correctly.
> Without seeinghte code it is pretty hard to say. One thing that when you a
re
> doing a lot of data manipulation you should ensure you have plenty of room
> in the log file and it should be placed on a different drive array than th
e
> data file. Preferably a Raid 1.
> --
> Andrew J. Kelly SQL MVP
>
> "culam" <culam@.discussions.microsoft.com> wrote in message
> news:465DB2E9-14AA-4AE2-B83F-A79C73458BD3@.microsoft.com...
>
>|||I doubt that is the case but anyway here are some links that may help you
pinpoint what the bottlenecks are.
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"culam" <culam@.discussions.microsoft.com> wrote in message
news:ED0F7189-D1C3-4B41-98EE-22F91FCCDB14@.microsoft.com...
> Unfortunately, we don't have access to the codes and the Client insists
> there
> codes is not the problem.
> How can I verify if log file is the problem?
> Culam
> "Andrew J. Kelly" wrote:
>
process data from 1 table and copy the data to multiple tables within the
same server. He said the preformance for others client run really fast,
except for ours. Our process run about 1 record (40 fields) per second. fo
r
table with 200K records, processing time is more than 8 hours, this is
unexceptable for our environment.
I am trying to figure out what I need to do within SQL server to speed up
the process.
Truncate log or Transactions?
Any help is greatly appreciate?
Thanks,
Culam8 Hours for 200K rows. That must be some really poor script. I don't know
what it is doing but it should only take a few minutes if done correctly.
Without seeinghte code it is pretty hard to say. One thing that when you are
doing a lot of data manipulation you should ensure you have plenty of room
in the log file and it should be placed on a different drive array than the
data file. Preferably a Raid 1.
Andrew J. Kelly SQL MVP
"culam" <culam@.discussions.microsoft.com> wrote in message
news:465DB2E9-14AA-4AE2-B83F-A79C73458BD3@.microsoft.com...
>A vendor wrote a php script to manipulate date within SQL Server, basically
> process data from 1 table and copy the data to multiple tables within the
> same server. He said the preformance for others client run really fast,
> except for ours. Our process run about 1 record (40 fields) per second.
> for
> table with 200K records, processing time is more than 8 hours, this is
> unexceptable for our environment.
> I am trying to figure out what I need to do within SQL server to speed up
> the process.
> Truncate log or Transactions?
> Any help is greatly appreciate?
> Thanks,
> Culam
>|||Unfortunately, we don't have access to the codes and the Client insists ther
e
codes is not the problem.
How can I verify if log file is the problem?
Culam
"Andrew J. Kelly" wrote:
> 8 Hours for 200K rows. That must be some really poor script. I don't kno
w
> what it is doing but it should only take a few minutes if done correctly.
> Without seeinghte code it is pretty hard to say. One thing that when you a
re
> doing a lot of data manipulation you should ensure you have plenty of room
> in the log file and it should be placed on a different drive array than th
e
> data file. Preferably a Raid 1.
> --
> Andrew J. Kelly SQL MVP
>
> "culam" <culam@.discussions.microsoft.com> wrote in message
> news:465DB2E9-14AA-4AE2-B83F-A79C73458BD3@.microsoft.com...
>
>|||I doubt that is the case but anyway here are some links that may help you
pinpoint what the bottlenecks are.
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"culam" <culam@.discussions.microsoft.com> wrote in message
news:ED0F7189-D1C3-4B41-98EE-22F91FCCDB14@.microsoft.com...
> Unfortunately, we don't have access to the codes and the Client insists
> there
> codes is not the problem.
> How can I verify if log file is the problem?
> Culam
> "Andrew J. Kelly" wrote:
>
Subscribe to:
Posts (Atom)