convert varchar to numeric with colon in data fails??

[GregVM]

DB is SQL 2000 and I'm trying to convert a field datatype from varchar to numeric where the data is hours:minutes:seconds (05:52:12), but I run into errors with EM and scripts that the db cannot convert those datatypes. I've tried the cast function with same results ... any solutions?? I need to run SUM(), AVG(), etc, against the data and can't with VARCHAR type.
Thanks all
-Greg

 

[r937]

what numeric value are you hoping to convert 05:52:12 to? whole hours? hours with a decimal portion? number of seconds?

cast it as a datetime or smalldatetime, then use DATEPART functions to get the hour, minutes, and seconds

rudy

http://rudy.ca/

 

[kishleo]

yes actually what is your purpose of converting the value into numeric, as there is : in the data you cannot convert it into numeric, use datepart to get the parts of the hours mins and secs seperately and manipulate them as you wish, or you can use convert but you have to convert your varchar data to datetime and then to numeric, if you do so, you will get a decimal equvalent of the time but i am not sure what you want.... ô¿ô Kishore

 

[GregVM]

thanks for the responses. the purpose is to provide stats on total time usage for each user on daily, weekly and monthly periods. If 1 user has logged into VPN 11 times that week, the syslogs will show a new record for each session with duration info in hours:minutes:seconds format, then I want to provide a detailed report on that activity. Decimal equivalents of the time is fine in such a case ... right???

thx

 

[13Echo]

Try the cast or convert, I have an exmple listed below.
SELECT d.intPlayerId,
p.vcPlayerName,
count(*) as numberofgames,
convert(numeric(10,2),
sum(d.intRank)) AS rank,
sum(convert(numeric(6,2),d.intKills)) AS kills,
sum(convert(numeric(6,2),d.intDeaths)) AS deaths,
cast(sum(d.intKills) as decimal) /SUM(d.intDeaths) AS ratio,
t.intTypeID

 

[GregVM]

that's the exact two functions I've been trying with no luck. Here's my current query:

SELECT dbo_syslogd_temp.syslog_ID, dbo_syslogd_temp.syslog_user,cast(sum(dbo_syslogd_temp.syslog_dur) AS SumOfsyslog_dur), dbo_Users.FullName
FROM dbo_Users RIGHT JOIN dbo_syslogd_temp ON dbo_Users.UserName = dbo_syslogd_temp.syslog_user
GROUP BY dbo_syslogd_temp.syslog_ID, dbo_syslogd_temp.syslog_user, dbo_Users.UserName, dbo_Users.FullName
ORDER BY dbo_syslogd_temp.syslog_user;

This fails with error "Syntax error (missing operator) in query expression 'cast(sum(dbo_syslogd_temp.syslog_dur) AS SumOfsyslog_dur)'.

If I remove the cast function, the query runs and shows the proper rows but shows every row for each user instead the total sum of hours:minutes:seconds per unique user. That's what I'm trying to acomplish ... then once 've got that down I'll be able to show more details (weekly, mnothly, etc) for each user.

Thanks

 

[r937]

SELECT dbo_syslogd_temp.syslog_ID
     , dbo_syslogd_temp.syslog_user
     , dbo_Users.FullName
     , sum(

       datepart( hh, cast(dbo_syslogd_temp.syslog_dur
                          AS smalldatetime)  ) * 3600
     + datepart( mi, cast(dbo_syslogd_temp.syslog_dur
                          AS smalldatetime)  ) * 60
     + datepart( ss, cast(dbo_syslogd_temp.syslog_dur
                      AS smalldatetime)  ) 

          ) / 3600.0 as total_hours
  FROM dbo_syslogd_temp 
LEFT 
  JOIN dbo_Users 
    ON dbo_syslogd_temp.syslog_user
     = dbo_Users.UserName 
GROUP
    BY dbo_syslogd_temp.syslog_ID
     , dbo_syslogd_temp.syslog_user
     , dbo_Users.FullName
ORDER 
    BY dbo_syslogd_temp.syslog_user

i changed it from a RIGHT to a LEFT join, it's way easier to understand (okay, maybe just for me)

rudy

 

[r937]

newsflash: smalldatetime is good only down to one minute

CAST as datetime instead

 

[GregVM]

I'l give it a swing ... thanks man!!!

 

[GregVM]

Here's the resulting error after I changed the cast to datetime:

SELECT dbo_syslogd_temp.syslog_ID, dbo_syslogd_temp.syslog_user, dbo_Users.FullName, sum(datepart(hh,cast(dbo_syslogd_temp.syslog_dur AS datetime)) * 3600 + datepart(mi,cast(dbo_syslogd_temp.syslog_dur AS datetime)) * 60 + datepart(ss,cast(dbo_syslogd_temp.syslog_dur AS datetime))) / 3600.0 as total_hours
FROM dbo_syslogd_temp
LEFT JOIN dbo_Users ON dbo_syslogd_temp.syslog_user = dbo_Users.UserName
GROUP BY dbo_syslogd_temp.syslog_ID, dbo_syslogd_temp.syslog_user, dbo_Users.FullName
ORDER BY dbo_syslogd_temp.syslog_user


Syntax error (missing operator) in query expression 'sum(datepart(hh,cast(dbo_syslogd_temp.syslog_dur AS datetime)) * 3600 + datepart(mi,cast(dbo_syslogd_temp.syslog_dur AS datetime)) * 60 + datepart(ss,cast(dbo_syslogd_temp.syslog_dur AS datetime))) / 3600.0'.

I can't figure out what may be missing??
-Greg

 

[r937]

i can't see the problem either, and i don't have sql/server to test on

could it be that CAST(expression AS DATETIME) needs a date as well as a time? i assumed it would default to january 1 1753 or something similarly irrelevant to this problem

anyone? buehler?