Query Problem -Datetime = Date

[karassik]

Hello All,

 

Thanks for looking at this.

 

Take a look then I\'ll tell you the problem.

 

SELECT idclinic_dblog, date_dblog, SUM(charges_dblog), SUM(pmnt_dblog), SUM(adjmnts_dblog)

FROM dlybatchlog_dblog

WHERE date_dblog =\'\".$tmpDate.\"\'

GROUP BY idclinic_dblog

 

My problem is that date_dblog is a datetime field and I would like to sort by idclinic and todays date. $tmpDate is todays date as \"m-d-Y\" not sure if I have to use \"Y-m-d\", but that is the part I am having problems with. Do I first have to convert date_dblog to the format that I am comparting it to in the WHERE? OR?

 

Thanks.

 

Nishan

[pallevillesen]

YES...

 

either convert the date_dblog so it is of the same format as your $tmpDate or the other way around....

 

You may use php to convert $tmpDate or sql to convert date_dblog.

 

Basically, think of it as a string comparison.

 

I hope this helps,

P.

[karassik]

Yes, I ended up finding the DATE_FORMAT to convert the date and it works perfectly.

[karassik]

So this worked perfectly until I added the $_SESSION portion.

 

SELECT idclinic_dblog, idcon_dblog, DATE_FORMAT(date_dblog,\'%m-%d-%Y\') as date_dblog, SUM(charges_dblog) as charges, SUM(pmnt_dblog) as payments, SUM(adjmnts_dblog) as adjustments

FROM dlybatchlog_dblog

GROUP BY date_dblog

HAVING date_dblog = DATE_FORMAT(now(),\'%m-%d-%Y\') AND idcon_dblog = \"\'.$_SESSION[\'KT_id_MSSIntranet\'].\'\"

 

I know that $_SESSION[\'KT_id_MSSIntranet\'] holds a number. Am I not using the correct Syntax? I tried a WHERE idcon_dblog = \"\'.$_SESSION[\'KT_id_MSSIntranet\'].\'\" instead of the AND but received an error.

 

Any insights?

[karassik]

I moved the HAVING \"AND ....\" to GROUP BY date_dblog, idcon_dblog = \"\'.$_SESSION[\'KT_id_MSSIntranet\'].\'\"

 

Thanks anyways.