Substring in my query

[trojnfn]

In my form, I dynamicall generate a pulldown, for months (1-Jan, 2-Feb, 3-Mar, etc.)
<select name="Month">
<option selected value="0">Scroll down to select month</option>
<cfoutput query="qryMonth">
<option value="#qrymoth.month#">#qrymonth.month#</option>
</cfoutput>
</select>

In my action page, for the query, I want to do something like this :
select substring(date_resolved,1,2) as month, substring(date_resolved,7,4) as year
where month = '#form.month#' and year = '#form.year#'

The date_resovled could be 1/1/2008 or 5/16/2008 (in the sql table)

When I use the substring to extract, am I getting one char for the month, or two (how would I know) ?
Also, it says the variable month is undefined.

What is the proper way to do this ?

Thanks

[cfcoder2]

It's hard to answer all your questions without seeing your code. But here's a shot at helping...
First off, format the date var "date_resolved" so that the format of it is known. This removes the question is it one or two digit day or month values. Use
TO_CHAR(date_resolved, 'MM/DD/YYYY') date_resolved
then MM and DD will always be two digits. Check for leading zero and remove, if you don't want to display it, etc.

Note that your <option> tag in your example has a typo... qrymoth.month vs qrymonth.month

It is hard to say why variable month is undefined without seeing your code.
Your select statement has no table defined either. Normally, you specify a table using the FROM clause.
If more than one table involved such as in a join, then specify the table names for all columns. That is probably why it's not finding "month"... it should have a table name in front of it such as table.month where table is your table name or an alias for it.

[Dan_Bracuk]

The first thing to do is get rid of the date_resolved column. With some database programs, Oracle for example, you can use db functions to convert dates to strings formatted any way you want. If your db does not have the necessary functionality, simply select the date and use Cold Fusion's date functions to format it.