DATEPART and DATEDIFF using VARCHAR(24) Date Format

My counterdatetime field format is varchar(24) listed below.
This format cannot be changed because it's output from perfmon. How can I
change the sql query to recognize DATEPART and DATEDIFF with my
counterdatetime field in varchar(24) format.
Please help me resolve the problem.
Thank You,
select a.counterdatetime, t.countername, avg (a.countervalue)
from counterdata a (NOLOCK),
counterdetails t (NOLOCK)
where a.counterdatetime > '2005-01-20'
AND a.CounterID = t.CounterID
AND t.countername like 'Data File(s) Size (KB)'
AND DATEPART(hh,a.counterdatetime) BETWEEN 8 AND 17
AND DATEPART(wday,a.counterdatetime) BETWEEN 2 AND 6
group by a.counterdatetime, t.countername
order by a.counterdatetime
Error:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
a.counterdatetime
2005-01-20 00:00:35.316
2005-01-20 00:01:35.316Joe,
You should have stored the data in the table as a datetime datatype iand not
a character. In any case try setting the dateformat and see if that helps:
SET DATEFORMAT YMD
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:55ABEE1A-2D7B-470E-9D93-AFDE09F135A6@.microsoft.com...
> My counterdatetime field format is varchar(24) listed below.
> This format cannot be changed because it's output from perfmon. How can I
> change the sql query to recognize DATEPART and DATEDIFF with my
> counterdatetime field in varchar(24) format.
> Please help me resolve the problem.
> Thank You,
> select a.counterdatetime, t.countername, avg (a.countervalue)
> from counterdata a (NOLOCK),
> counterdetails t (NOLOCK)
> where a.counterdatetime > '2005-01-20'
> AND a.CounterID = t.CounterID
> AND t.countername like 'Data File(s) Size (KB)'
> AND DATEPART(hh,a.counterdatetime) BETWEEN 8 AND 17
> AND DATEPART(wday,a.counterdatetime) BETWEEN 2 AND 6
> group by a.counterdatetime, t.countername
> order by a.counterdatetime
> Error:
> Server: Msg 241, Level 16, State 1, Line 1
> Syntax error converting datetime from character string.
> a.counterdatetime
> 2005-01-20 00:00:35.316
> 2005-01-20 00:01:35.316
>
>|||try this
convert(datetime,@.counterdatetime, 101)
Thanks,
RK
"Joe K." wrote:

> My counterdatetime field format is varchar(24) listed below.
> This format cannot be changed because it's output from perfmon. How can I
> change the sql query to recognize DATEPART and DATEDIFF with my
> counterdatetime field in varchar(24) format.
> Please help me resolve the problem.
> Thank You,
> select a.counterdatetime, t.countername, avg (a.countervalue)
> from counterdata a (NOLOCK),
> counterdetails t (NOLOCK)
> where a.counterdatetime > '2005-01-20'
> AND a.CounterID = t.CounterID
> AND t.countername like 'Data File(s) Size (KB)'
> AND DATEPART(hh,a.counterdatetime) BETWEEN 8 AND 17
> AND DATEPART(wday,a.counterdatetime) BETWEEN 2 AND 6
> group by a.counterdatetime, t.countername
> order by a.counterdatetime
> Error:
> Server: Msg 241, Level 16, State 1, Line 1
> Syntax error converting datetime from character string.
> a.counterdatetime
> 2005-01-20 00:00:35.316
> 2005-01-20 00:01:35.316
>
>