Convert Function in MySQL

[jjprabhu]

Hello everyone,

Earlier when i used sugarcrm 4.5 with MSSQL, i had used the following query for generating a report:


select o.name as "Opp. name",u.first_name+' '+u.last_name as "Opp. Assigned to"
from opportunities o,users u
where sales_stage='Prospecting'
and u.id=o.assigned_user_id
and convert(datetime,convert(char(11),o.date_entered,1 01)) >= convert(datetime,$P{startdate},101)
AND convert(datetime,convert(char(11),o.date_entered,1 01)) <= convert(datetime,$P{enddate},101)
group by o.name,u.first_name,u.last_name
order by u.first_name,u.last_name


But now when im using Mysql it is throwing some error:

are there any syntax changes in the convert() for MySQL...

Please help me in solving this issue

Thanks in advance

[mikesolomon]

Try

select o.name as "Opp. name",u.first_name+' '+u.last_name as "Opp. Assigned to"
from opportunities o,users u
where sales_stage='Prospecting'
and u.id=o.assigned_user_id
and DATE(o.date_entered) between $P{startdate}, AND $P{enddate}
group by o.name,u.first_name,u.last_name
order by u.first_name,u.last_name

[agcopley]

Hi there,
A look in the mysql manual gives convert

CONVERT(expr,type), CONVERT(expr USING transcoding_name)

where type = datetime etc.

it appears they have reversed the order of the parameters for your function.


Rgds
Andrew

[jjprabhu]

thanks mikesolomon for your prompt reply...

I have come up across yet another issue......

suppose a Team leader wants a count of the leads which are assigned to his team members(ie. users)..
How can this be done......

I have used the following query........

select l.first_name "Lead's First name",l.last_name "Lead's Last name",u.user_name "User's name" from users u,leads l, teams t, team_membership tm
where
u.id= "$SUGAR_USER_ID"
u.id=l.assigned_user_id
and u.id=tm.user_id
and t.id=tm.team_id