Skip to main content
A
Anonymous
July 6, 2006
Answered

formatting a date in cfselect

  • July 6, 2006
  • 3 replies
  • 814 views
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...
This topic has been closed for replies.
Correct answer
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>

3 replies

A
Anonymous
Correct answer
July 6, 2006
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>
A
Anonymous
July 6, 2006
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.
A
Anonymous
July 6, 2006
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...
A
Anonymous
July 6, 2006
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).

N
Newsgroup_User
Inspiring
July 6, 2006
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...
>
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices