Date_Time Convert(24) to DateTime Format

Please help me modify the sql statement so that I average wly values for
FullScan and CPU.
My Date_Time and samples are written to a table with the
char(24) format.
I need to convert the Date_Time field from char(24) to datetime format.
Please help me with this task.
Thanks,
Date_Time char(24)
2005-01-24 16:06:48.966
2005-01-24 16:07:48.966
2005-01-24 16:08:48.966
select
dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20050202') as HourStart,
avg([Full_Scan_Sec]) as FullScan,
avg([CPU_Processor_Time]) as CPU
from Server_Data
where date_time BETWEEN '20040802' and '20050202'
group by dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20040802')
order by dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20050202')Joe
Have you tried CONVERT system function?
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:42A097A1-85E1-4252-A39E-5A7320E65268@.microsoft.com...
> Please help me modify the sql statement so that I average wly values
for
> FullScan and CPU.
> My Date_Time and samples are written to a table with the
> char(24) format.
> I need to convert the Date_Time field from char(24) to datetime format.
> Please help me with this task.
> Thanks,
>
> Date_Time char(24)
> 2005-01-24 16:06:48.966
> 2005-01-24 16:07:48.966
> 2005-01-24 16:08:48.966
> select
> dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20050202') as
HourStart,
> avg([Full_Scan_Sec]) as FullScan,
> avg([CPU_Processor_Time]) as CPU
> from Server_Data
> where date_time BETWEEN '20040802' and '20050202'
> group by dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20040802')
> order by dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20050202')
>|||I tried to convert the Date_Time from char(24) to smalldatetime received
syntax error.
select counterDateTime from CounterData (NOLOCK)
where convert(smalldatetime,counterdatetime)
Line 2: Incorrect syntax near ')'.
Please help me resolve this problem.
Thank You,
"Uri Dimant" wrote:

> Joe
> Have you tried CONVERT system function?
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:42A097A1-85E1-4252-A39E-5A7320E65268@.microsoft.com...
> for
> HourStart,
>
>|||> select counterDateTime from CounterData (NOLOCK)
> where convert(smalldatetime,counterdatetime)
Abive is not a valid WHERE clause. It is similar to saying:
WHERE colname
A WHERE clause need some predicate, like:
WHERE colname = 23
What do you want to achieve? Return only the rows in where you have a string
in the column that can
be converted to datetime? If so, try:
WHERE ISDATE(counterdatetime) = 1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:84F6AF1A-E00B-416D-95CB-F5A3C05054BD@.microsoft.com...
> I tried to convert the Date_Time from char(24) to smalldatetime received
> syntax error.
> select counterDateTime from CounterData (NOLOCK)
> where convert(smalldatetime,counterdatetime)
> Line 2: Incorrect syntax near ')'.
> Please help me resolve this problem.
> Thank You,
>
>
> "Uri Dimant" wrote:
>|||I'm trying to convert the DateTime column format from char(24) to
smalldatetime in a sql query. When it's in smalldatetime format then use the
average function to calculate average full_scan and CPU values.
The sql statements listed below.
Thank You,
"Tibor Karaszi" wrote:

> Abive is not a valid WHERE clause. It is similar to saying:
> WHERE colname
> A WHERE clause need some predicate, like:
> WHERE colname = 23
> What do you want to achieve? Return only the rows in where you have a stri
ng in the column that can
> be converted to datetime? If so, try:
> WHERE ISDATE(counterdatetime) = 1
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:84F6AF1A-E00B-416D-95CB-F5A3C05054BD@.microsoft.com...
>
>|||I'm not sure exactly what your problem is. You posted a query in the origina
l post, but you didn't
say what happens when you run the query. Are you saying that below part fail
s? If so, what error
message do you get? Or incorrect results?
dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20050202')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:4D4CBFAA-70AB-498A-BBE4-3F9497793250@.microsoft.com...
> I'm trying to convert the DateTime column format from char(24) to
> smalldatetime in a sql query. When it's in smalldatetime format then use t
he
> average function to calculate average full_scan and CPU values.
> The sql statements listed below.
> Thank You,
>
> "Tibor Karaszi" wrote:
>