Copy link to clipboard
Copied
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
</cfquery>
RESULTS IN:
{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.
Thank!
Copy link to clipboard
Copied
Not sure what you googled, but when I googled "mysql date functions", the first offering included something about a function called get_format which returns a string.
Copy link to clipboard
Copied
Thanks for pointing that out. I was playing DATE_FORMAT to convert to a string
DATE_FORMAT(p.intakeDate, '%Y-%m-%d') as intakeDate