CODEWITHSUNDEEP
coldfusion
user3591637
user3591637

Reputation: 499

How to make date and time form values insert into one column?

I have a form where the user selects a date and time. The user's selections represent the time and date they want specific answers, which they enter in the same form, to display. Then I grab the selected values and insert them into a table: cse_result_summary. Right now my code is inserting them correctly.

I'm using this jquery plugin for the date. Time is just a simple select. Something I did not think about before I finish the form was how I would compare today's date to the show_date.

The code below will work for the show_date, but the problem is with the time. Even if the date is greater, but the time is not, it will have to wait for that time to display. I would like it to display at that specific time and date. When its after that date and time it should no longer matter the time it is.

Since I'm inserting the date and time in different columns, I'm wondering if its possible to insert them into one column? That way I will only have to compare todayDate > formDate:

<cfset dtToday = Now() />
<cfif DateFormat(dtToday, "yyyy--mm--dd") gte DateFormat(getdates.show_date) 
     &&  TimeFormat(dtToday) gte timeformat(getdates.show_time)>

different file (form file)

<tr>
    <td> Date it will display (Please enter date format mm/dd/yyyy.):</td>
    <td><input class="inputDate" id="inputDate" 
              value="07/01/2014" NAME="date_used"/>
    </td>
</tr>
<tr>
    <td> Time it will display (Please enter time format hh:mm tt):         
    <td><select ID="time_used" NAME="time_used"  VALIDATE="date" >
            <option selected> Select Time</option>
            <option value="7:00 AM">7:00 AM</option>   
            <option value="7:15 AM">7:15 AM</option> 
            <option value="7:30 AM">7:30 AM</option>   
            <option value="7:45 AM">7:45 AM</option>
            <option value="8:00 AM">8:00 AM</option>
            </select></TD>
    </td>
</tr>
</tbody>
</table>

<p><input type="submit" name="Submit" value="Submit"></p>
</form>

<cfif FormSubmit eq "FormSubmitted">
    ... more code....
    <cfset  month_date_show = #DateFormat(Trim(form.month_date_show), "mm-15-yyyy")#>
    <cfset newdate = #DateFormat(Trim(date_used), "mm-dd-yyyy")# />
    <cfset time_used = #TimeFormat(Trim(time_used),"h:mm tt")#>

    <cfquery datasource="Intranet" name="InsertRequest">
        INSERT INTO cse_result_summary 
        ( show_date, show_time,monthly_enter_date,monthly_past_date )
        VALUES 
        ( '#newdate#','#time_used#',getdate(),'#month_date_show#' )
    </cfquery>

Upvotes: 1

Views: 456

Answers (1)

steve
steve

Reputation: 1490

Assuming your database field type is date/time you could do something like this:

<tr>
                    <td> Date it will display (Please enter date format mm/dd/yyyy.):</td>
                    <td><input class="inputDate" id="inputDate" value="07/01/2014" NAME="date_used"/>
                </td>
                </tr>
                <tr>
                    <td> Time it will display (Please enter time format hh:mm tt):         
                <td><select ID="time_used" NAME="time_used"  VALIDATE="date" >
                 <option selected> Select Time</option>
                    <option value="7:00 AM">7:00 AM</option>   
                    <option value="7:15 AM">7:15 AM</option> 
                    <option value="7:30 AM">7:30 AM</option>   
                    <option value="7:45 AM">7:45 AM</option>
                    <option value="8:00 AM">8:00 AM</option>
                    </select></TD>
                </td>
                </tr>


            </tbody>
            </table>

            <p><input type="submit" name="Submit" value="Submit"></p>
            </form>

<cfif FormSubmit eq "FormSubmitted">
... more code....
<cfset  month_date_show = #DateFormat(Trim(form.month_date_show), "mm-15-yyyy")#>
<cfset newdate = #DateFormat(Trim(date_used), "mm-dd-yyyy")# />
<cfset time_used = #TimeFormat(Trim(time_used),"h:mm tt")#>

<Cfset combinedDateTime="#newdate #time_used#">



            <cfquery datasource="Intranet" name="InsertRequest">
            INSERT INTO cse_result_summary (show_date, show_time,monthly_enter_date,monthly_past_date, *yourcolumname*)
            VALUES ('#newdate#','#time_used#',getdate(),'#month_date_show#', <cfqueryparam cfsqltype="cf_sql_timestamp" value="#combinedDateTime#">)
            </cfquery>

Upvotes: 1

Related Questions