SfC Home > Web Design > SQL > ColdFusion >

Dealing with Dates and Times in ColdFusion

by Ron Kurtus (revised 17 December 2001)

Dates and times are used in many ColdFusion applications. Depending on how your database is set up, you may have to use special formatting to be see the dates correctly, to use them as a filter in a query, or when submitted through a form.

Questions you may have include:

How do you display the output in the proper format?
How do you reformat the date to use in a query?
How do you insert or update a date or time through a form?

This lesson will answer those questions.

Displaying output of query

The output of your query depends on the type of data fields you use in your database. If you are using a date/time field, you may have to format your output.

Dates and times in database

You can enter dates and/or times in a database in either a text field or a date/time field.

Text field

If your database has dates and/or times located in a text field or column, there are no problems when you query the database for those items. The date is simply considered text.

Date/time field

Usually dates and times are entered in a date/time database column. This allows you to use other date/time functions, such as making comparisons, counting days, etc. Unfortunately, there are some database quirks concerning inputting and outputting data in this format that can cause problems.

ColdFusion output

Although your database may store the date as a short date of date/time type, the output of a ColdFusion query will often result in the date being in the DateTime format of 2001-02-18 00:00:00. To display the date, you must reformat it.

Check database output format

A good idea is to query for a specific date and time and then check the output to see what form it is in.

<CFQUERY DATASOURCE="xxx" NAME="info">
SELECT date_used, time_used
FROM invoices
WHERE ID = 1
</CFQUERY>

Then check the output:

If the date is in the format similar to 2001-02-18 00:00:00, it is in the DateTime format, and you must format the date to view or use it. Likewise, if the time looks like 1899-12-30 19:00:00, it must be formatted.

Display in proper format

To display the date and time in the proper format, you can use:

<CFOUTPUT QUERY="info">
#DATEFORMAT(date_used, "m/d/yyyy")#<BR>
#TIMEFORMAT(time_used, "h:mm tt")#
</CFOUTPUT>

The output should result in something like:

3/25/2001
2:30 PM

Using date as filter in a query

In order to use a date or time as a filter in a SQL query, it must be in an ODBC format. If you have seen its output is in the DateTime format, you should reformat your date using the CREATEODBCDATETIME and CREATEODBCTIME functions to put the them in a form that is readable through a SQL query.

Format for ODBC

For example, you could set 3/25/2001 as a new_date variable and 7:30 PM as a new_time variable in this ODBC format.

Filter query

Then you can filter your query:

<CFQUERY DATASOURCE="xxx" NAME="activities">
SELECT * FROM activefield
WHERE date_used <= #new_date#
AND time_used > #new_time#
</CFQUERY>

Reformat in output

When you want to display the output, you must format the date and time for the proper appearance:

<CFOUTPUT QUERY="activities">
#DATEFORMAT(date_used, "m/d/yyyy")#<BR>
#TIMEFORMAT(time_used, "h:mm tt")#
</CFOUTPUT>

The output would look something like:

3/25/2001
8:30 PM etc.

Submitting date and time through a form

If you want to submit a date and time through a form, it is good to validate that the user entered the date in a correct format. You use the <CFFORM> tag in order to do the validation.

Validate input

The following code illustrates how to validate date and time inputs:

<CFFORM METHOD="POST" ACTION="actionpage.cfm">
<P>Enter Date (mm/dd/yyyy) i.e 12/5/2000
<CFINPUT TYPE="text" NAME="date_used" SIZE="10" REQUIRED="yes" VALIDATE="date" MESSAGE="Please enter date format mm/dd/yyyy."></P>
<P>Enter Time (hh:mm) i.e 7:35
<CFINPUT TYPE="text" NAME="time_used" SIZE="10" REQUIRED="yes" VALIDATE="time" MESSAGE="Please enter time format as hh:mm."></P>
<INPUT TYPE="submit" NAME="Submit">
</CFFORM>

Validate date

VALIDATE="date" results in validating that what what is typed in the text box is in the proper date format. If it is not, a warning message will pop up when the Submit button is pressed. Note that ColdFusion allows the year to be either 2001 or 01.

REQUIRED="yes" will ensure the user inputs data in the text box. If no data is submitted, the warning message will display.

Validate time

The only problem with validating time is that ColdFusion uses a 24 hour clock, instead of AM and PM. It might be better not to validate the time and hope the user enters the data correctly.

Sample form

The form would appear as below. Since this is an HTML page, instead of a CFML page, the functionality of validation is not available here.

Another method

You can also check that the date sent through the form is in the correct format in the action.cfm page, by including code similar to:

<CFIF IsDate(form.date_used) IS "No">
Please enter your date in the format MM/DD/YYYY where
the month, day, and year are all numerical digits.<BR>
One of your dates is not correct. <BR>
Please hit your Browser back button and correct the entry.<BR>
<CFSET error = "yes">
</CFIF>

This method seems a little awkward, but it may be useful in some situations.

Inserting or updating dates and times

If you are going to submit a form to insert a date or time in a database table column, or you are going to update a record with a date or time, you must reformat the data before making the insertion or update.

The reason is that the <INPUT> operation sends the date and time data as text, which usually will not be in the correct format for the database. This means that you cannot use the <CFINSERT> or <CFUPDATE> tags to perform the task. Rather, it must be done through a query, using SQL commands.

Submit action page

Let us submit a form to the action page, inputting a date and time.

Set format for SQL

Then, before the data is inserted or updated, it must be set into the proper format for use in SQL.

Insert data

Because you have created new functions, you cannot use the CFINSERT or CFUPDATE tags. They automatically use what was sent via the form to this page. Instead, you must use a query and then insert or update with SQL commands:

<CFQUERY DATASOURCE="xxx">
INSERT date_used, time_used
INTO activefield
WHERE date_used = #new_date#
AND time_used = #new_time#
</CFQUERY>

Updating is done the same way.

Summary

It is a good idea to check to see how your data looks from a raw query. Then you can format the date and time data into the proper format. When you submit a date or time through a form, you can validate that they are being sent in the proper format in your form. To use a date or time to filer in a query or to insert or update the date or time in the database, you must set the item to be in the ODBC format to properly use it with the database.

Seek to better society with your work

Resources and references

Ron Kurtus' Credentials

Websites

ColdFusion Resources

Books

Top-rated books on ColdFusion

Questions and comments

Do you have any questions, comments, or opinions on this subject? If so, send an email with your feedback. I will try to get back to you as soon as possible.

Share this page

Click on a button to bookmark or share this page through Twitter, Facebook, email, or other services:

Students and researchers

The Web address of this page is:
www.school-for-champions.com/coldfusion/
dates.htm

Please include it as a link on your website or as a reference in your report, document, or thesis.

Where are you now?

School for Champions

ColdFusion topics

Dealing with Dates and Times