View Full Version : Storing ColdFusion date object into a relational database?

04-15-2012, 02:40 AM
I am trying to write an Auction website using HTML and ColdFusion powered by a relational database. It's an exercise/experiment that I've come up with.

ColdFusion has the Now() function that returns the current time on the server. I want to take that time, run it through the TimeFormat function and then store that in an SQL table field.

We are using MsSQL server and writing the queries in ColdFusion. I am programming the entire site using Visual Studio and will be managing the database using Visual Studio.

I don't know how I would store the formatted ColdFusion time object in the relational database. I need this information to determine when an auction has "expired".

I'm not sure how I could "actively" make this work.

For instance... if it reaches 5PM and the auction is set to 5PM technically the auction would be "over" but the buyer and seller would not know until another even happened. I don't know how to make the site "dynamic" in the fact that when the server time reaches 5 PM, something would happen that "ends" all the applicable auctions, creates an invoice for the buyer and notifies the seller that their item has sold and for how much.

That's further down the road though. For right now I need to figure out how to insert into a ColdFusion date/time object into my database table (hypothetically named "Auctions").

Unless there is a much better way to handle auctions "ending". I plan on allowing the user to select three different time spans for auctions (1 day, 3 day and 5 day) with the administrator having a "test" time span option of 60 seconds.

04-15-2012, 06:15 PM
When you pass a formatted date/time string into a DATETIME column, SQL Server will automatically convert it to its underlying date format. To use a format that should be able to be read by SQL Server, use the createODBCDateTime() function.

So for example:

<cfset myDate = now()>

<cfquery datasource="mydsn">
INSERT INTO myTable ( myDateCol )
VALUES ( '#createODBCDateTime( myDate )#' )

For your "auction ending" feature, one way to do it is to create a ColdFusion scheduled task which runs a .cfm page maybe every minute or so. This .cfm page would check the database for expired auctions that haven't yet been processed, and send out the appropriate emails and such. (You could also have some other non-ColdFusion solution for this on your server, but this would probably be easiest.)

04-15-2012, 07:04 PM
Thanks, your post was very helpful.

Would you happen to know how it would work to get the OCDBDateTime timeobject back into a standard ColdFusion DateTime object or am I missing something obvious?

Also are there any good resources that can get me started on creating the ColdFusion scheduled task? I can probably google it and stumble onto something, I just write this as I am about to fly out the door to attend a mandatory college event :/

Thanks again though!

04-16-2012, 01:14 AM
You often don't need to actually work with a date/time object in ColdFusion. If you want to extract the month/day/year/time parts, you can simply use the month()/day()/year() (and other) functions on a string which is formatted as a date. ColdFusion will figure it out (parsing the date/time behind the scenes).

However, it may be a bit more efficient for you to manually parse a date/time string first before calling the other functions on it (so that ColdFusion doesn't have to parse it each time). For that, you can use the parseDateTime() function. Ex:

<cfset myDate = parseDateTime( myQuery.dateCol )>
<cfset theDay = day( myDate )>
<cfset theMonth = month( myDate )>
<cfset theYear = year( myDate )>

There are also functions for retrieving the time parts, and the dateFormat() function which is very straightforward and useful for displaying formatted date/times (which also doesn't require a parseDateTime() call beforehand).

The list of date/time functions is here: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec1a60c-7ffc.html#WSc3ff6d0ea77859461172e0811cbec22c24-6986


Nothing comes to mind right off the bat for a tutorial on scheduled tasks, but if you have access to the ColdFusion administrator on your server, you can set them up right from there. Otherwise there's the <cfschedule> tag for setting one up.

Scheduled tasks are really just as simple as the server running a .cfm file at a given url, on a given time interval. The server will run the .cfm file just like it would any other .cfm file, so that's where you'd put your processing.

The generated output from the file (if any) won't be visible though (as you're not manually loading up the .cfm page in a web browser), but you can have it be sent to a log file for later inspection if needed.

04-20-2012, 07:00 PM
I am getting an error when trying to insert the OCDBDateTime object into my database table.

The Table name is "Time", The field name is "Date"

Here is my code snippit:


<cfset myDate = now() />

<cfset myTableDate = #createODBCDateTime( myDate )# />

My Date: #myDate# <br/>
Table Date: #myTableDate# <br/>

<cfquery name="qrytime" datasource="exampledsn" username="exampleuser" password="password" >
Time (Date)
Value ( '#myTableDate#')

<cfquery name="qryshowtime" datasource="exampledsn" username="user" password="password" >



This is the error generated by the server:

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'Value'.

The error occurred in TestDateTime.cfm: line 11
Called from Index.cfm: line 143
Called fromTestDateTime.cfm: line 11
Called from Index.cfm: line 143

9 : Table Date: #myTableDate# <br/>
10 :
11 : <cfquery name="qrytime" datasource="exampledsn" username="user" password="mypassword" >
13 : Time (Date)

SQL INSERT INTO Time (Date) Value ( getdate())
DATASOURCE exampledsn

I do not know why the table does not like this date/time object.

I have read that the datetime object in SQL server is like 8 characters only?
I think the error of incorrect syntax near "value" is strange?

04-20-2012, 07:29 PM
Ok, a few problems:

First, an insert statement uses the keyword "values", not "value" (even if there is only one value that you are inserting). Your SQL should look like this:

<cfquery name="qrytime" datasource="exampledsn" username="exampleuser" password="password" >
INSERT INTO Time ( Date )
VALUES ( '#myTableDate#' )
So it's not necessarily an issue with your date format, although we don't know just yet because you need the correct SQL syntax first :)

Second, there is an issue with using words DATE and TIME for your table/column names. They are datatype keywords, and may or may not cause the parser to complain with where you're using them. In general, you shouldn't name columns or tables with these names.

And finally, you've read that the DATETIME datatype is stored using 8 bytes of space behind the scenes; not that it can only accept a format of 8 characters. It would be very hard to provide SQL Server of a full date/time value (such as in the format of "mm/dd/yyyy hh:mm:ss zzzz") in just 8 characters ;-)