Skip to main content
D
doug123
Participating Frequently
September 28, 2008
Answered

cfscript and mysql date

  • September 28, 2008
  • 2 replies
  • 438 views
I have page on my family's web site that has an option for downloading a .csv file that's a list of all the family members and is created using the cfscript I found at: http://cflib.org/udf/QueryToCSV2
This is done so other family members can import the data into Outlook, Thunderbird, etc.

I'm using MySQL 5.0 as my database and when members register for the site, I have their birth date stored in the "BirthDate" column using the DATE format. When the fore mentioned UDF creates the .csv file, the BirthDate column of that is presented similar to {ts '2008-06-18}. I searched the forums and found, what I believe is a similar problem, at this forum post

I realize that mysql has date functions, but to me, that seems more on the insert query side than on the select query side.

So my problem is, how would I properly format the date in that <cfscript> (UDF) tag for the query so it appears as 2007-06-18 in the .csv file?

This topic has been closed for replies.
Correct answer Newsgroup_User
you would use mysql DATE() function to return just the date part form a
datetime or timestamp field:

SELECT ..., DATE(your_datetime_field) AS some_column_alias

DATE(your_datetime_field) will return a date in YYYY-MM-DD format.


if, for some reason, the date in your db is stored not in default mysql
format, then you can use DATE_FORMAT() function to format the date:

SELECT ..., DATE_FORMAT(your_date_field, '%Y-%c-%e') AS
some_column_alias, ...
FROM ...

the '%Y-%c-%e' format will return 2008-1-1. if you need your date with
leading 0 in month and day, use '%Y-%m-%d' instead.

hth

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

2 replies

D
doug123Author
Participating Frequently
September 29, 2008
Thanks Azadi,

I guess after all I didn't really know about the date functions in MySQL.

It worked perfectly.
N
Newsgroup_UserCorrect answer
Inspiring
September 28, 2008
you would use mysql DATE() function to return just the date part form a
datetime or timestamp field:

SELECT ..., DATE(your_datetime_field) AS some_column_alias

DATE(your_datetime_field) will return a date in YYYY-MM-DD format.


if, for some reason, the date in your db is stored not in default mysql
format, then you can use DATE_FORMAT() function to format the date:

SELECT ..., DATE_FORMAT(your_date_field, '%Y-%c-%e') AS
some_column_alias, ...
FROM ...

the '%Y-%c-%e' format will return 2008-1-1. if you need your date with
leading 0 in month and day, use '%Y-%m-%d' instead.

hth

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices