Jump to content


Photo

Query Problem -Datetime = Date


  • Please log in to reply
4 replies to this topic

#1 karassik

karassik
  • Members
  • PipPip
  • Member
  • 13 posts
  • LocationPortland, Oregon

Posted 12 June 2003 - 12:45 AM

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

#2 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 12 June 2003 - 08:10 AM

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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#3 karassik

karassik
  • Members
  • PipPip
  • Member
  • 13 posts
  • LocationPortland, Oregon

Posted 12 June 2003 - 04:15 PM

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

#4 karassik

karassik
  • Members
  • PipPip
  • Member
  • 13 posts
  • LocationPortland, Oregon

Posted 12 June 2003 - 05:32 PM

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?

#5 karassik

karassik
  • Members
  • PipPip
  • Member
  • 13 posts
  • LocationPortland, Oregon

Posted 12 June 2003 - 06:34 PM

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

Thanks anyways.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users