convert from mysql datetime to coldfusion datetime

Question

Is anyone aware of a simple way to convert mysql datetime values to coldfusion datetime values in CF8 (and it may have to be backwards compatible with CF6)?

I need to store date times in mysql and have chosen to store them in mysql datetime format so I can get the db to do date ranges and comparisons for me. I could so this stuff in CF, but I'd have to retrieve potentially huge result set to process. Seems much saner to get the db to deal with this for me.

My front end code is coldfusion, and converting the cf date time objects into something mysql understands on insert is easy enough. But I've run into trouble bringing them back the other way.

There is the CreateDateTime function, but I would first have to split the mysql result up into the separate year, month, day, etc parts first. That's not hard, but seems needlessly complicated compared to converting the dates in the other direction.

The ParseDateTime function is no good to me either because I want to do further processing on a datetime object server side before formatting and sending to the client.

Does anyone know of a simple way to do this? Or do I have to settle for an easy conversion one way, and a kludgy one the other way?

Answer 1

you can use CreateODBCDateTime(date) function for this.

Answer 2

date_format is very good for this:

date_format(myDateCol, '%m/%d/%Y')

Moreover, if you need to date calculations on this, date_format can just be your wrapper:

date_format(date_add(myDateCol, interval 7 day), '%m/%d/%Y')

That will format the date for a week from now (+7 days) into the ColdFusion format.