2 Replies Latest reply on Feb 11, 2012 9:08 AM by wmkolcz

    Getting just the date out of MySQL with CF8

    wmkolcz Level 1

      I have a mysql table that includes a column 'intakeDate' set to the data type of DATE. When entering, it creates a record of YYYY-MM-DD. When I query the database with Coldfusion for the date of 2004-06-10, I get the result of {ts '2004-06-10 00:00:00'}. Fine and dandy for people in my timezone, but I found that if my users are in any timezone west of Eastern, the date slips back to the day before and their 'time stamp' is 23:00:00 (or appropriate offset). How can I get Coldfusion to just return to my Flex application only the value inside the table and not its ts version with midnight attached. Again, the table is set to only DATE and not TIMESTAMP or DATETIME so there are no time values entered. Also, mind you that this is being sent to a Flex application's manage class. Since some of this information deals with shot records and medical records, date is very important.


      I tried the MySQL function of DATE but it doesn't change the output.


      <cfquery name="list" datasource="#request.dsn#">

          Select DATE(intakeDate) as intakeDate FROM pets





      {ts '2010-12-29 00:00:00'}   {ts '2010-11-18 00:00:00'}    {ts '2009-12-28 00:00:00'}   {ts '2009-10-03 00:00:00'}   {ts '2009-07-13 00:00:00'}   {ts '2009-10-03 00:00:00'}    {ts '2008-06-01 00:00:00'}   {ts '2008-02-09 00:00:00'}   {ts '2003-03-01 00:00:00'}

      {ts '2004-06-10 00:00:00'}   {ts '2003-03-01 00:00:00'}    {ts '2004-06-01 00:00:00'}   {ts '2001-06-01 00:00:00'}   {ts '2010-04-01 00:00:00'}   {ts '2011-04-06 00:00:00'}    {ts '2011-04-06 00:00:00'}    {ts '2004-11-01 00:00:00'}    {ts '2010-05-04 00:00:00'}, etc


      I've been googling it all day and nothing I see if working.