2 Replies Latest reply on Sep 28, 2008 8:10 PM by doug123

    cfscript and mysql date

      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?

        • 1. Re: cfscript and mysql date
          Level 7
          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.


          Azadi Saryev
          • 2. Re: cfscript and mysql date
            doug123 Level 1
            Thanks Azadi,

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

            It worked perfectly.