formatting a date in cfselect

[rhellewell]

Assuming a query "meetingdate" that grabs a unique value of the 'meetingdate'
field (a date/time field). In a form, that query is used to populate a cfselect
command.

The dropdown boxes now contain "yyyy-mm-dd hh:mm:ss" value (as in "2006-06-12
12:23").

How do I format the drop-down values into 'mm/dd/yy", as in "06/12/06" ? Using
the formatdate in the 'display' argument of the cfselect command returns an
error.

Or, should the query command contain some sort of formatdate function (and
what would that be)?

Thanks....Rick...

[Ian Skinner]

Use appropriate SQL functions to format the field in the query, would
depend on the DBMS system you are using (MSSql, MYSql, Access, Oracle, etc).

OR

Use <select> instead of <cfselect> where you can use a basic loop to
populate the <option> tags and format the date in the loop.

OR

Use a Query of Query or manually build a query out of the original query
modifying the format of the date string and use this new query in the
<cfselect> tag.


rhellewell wrote:

> Assuming a query "meetingdate" that grabs a unique value of the 'meetingdate'
> field (a date/time field). In a form, that query is used to populate a cfselect
> command.
>
> The dropdown boxes now contain "yyyy-mm-dd hh:mm:ss" value (as in "2006-06-12
> 12:23").
>
> How do I format the drop-down values into 'mm/dd/yy", as in "06/12/06" ? Using
> the formatdate in the 'display' argument of the cfselect command returns an
> error.
>
> Or, should the query command contain some sort of formatdate function (and
> what would that be)?
>
> Thanks....Rick...
>

[MikerRoo]

It's always better to do this kind of thing in the query.

Here's the MS access syntax:
SELECT
Format(DATE_COLUMN, "mm/dd/yy") AS PurtyDate
FROM
YOUR_TABLE


Other DB's are similar.

[rhellewell]

This works (thanks!)
SELECT DISTINCT
Format(DATE_COLUMN, "mm/dd/yy") AS PurtyDate
FROM
YOUR_TABLE

but this doesn't
SELECT DISTINCT
Format(DATE_COLUMN, "mm/dd/yy") AS PurtyDate
FROM
YOUR_TABLE
ORDER BY DATE_COLUMN

and this doesn't
SELECT DISTINCT
Format(DATE_COLUMN, "mm/dd/yy") AS PurtyDate
FROM
YOUR_TABLE
ORDER BY Format(DATE_COLUMN, "mm/dd/yy")

Both throw the error:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] ORDER BY clause (meetingdate) conflicts with DISTINCT.

(Intent is to have unique date_columns, sorted by date_columns, formatted as
'mm/dd/yy')

...Rick...

[MikerRoo]

SELECT DISTINCT Format(DATE_COLUMN,"mm/dd/yy") AS PurtyDate
FROM YOUR_TABLE
ORDER BY Format(DATE_COLUMN,"mm/dd/yy")

does work.

You must have something else going on with your real query.

Attach the real query and attach the full error message (which includes
generated SQL).

[rhellewell]

This works (I may have typo'd the problem earlier)

SELECT
DISTINCT format(meetingdate,'m/dd/yy') as cmeetingdate
from documents
ORDER BY format(meetingdate,'m/dd/yy')

with this (note the use of the alias for the display and value parameters

<cfselect name="MeetingDate"
size="1"
message="Select the Meeting Date from the drop-down list"
query="MeetingDateList"
value="cmeetingdate"
display="cmeetingdate"
queryPosition="below"
width="10">
<option value="">*** any meeting date ***</option>
</cfselect>