Coldfusion dateformat not storing correctly in access database

I'm using Access to store my data. I have a table with a variable "ViewDate" that set to Date/Time. In coldfusion when I do a insert query with dateformat it's only display the time in the database. For example:

My code:
<cfquery name="Add_Click" datasource="MBG">
(PackageID, ViewDate, NumView)
(#url.Num#, #DateFormat(Now(), "mm/dd/yy")#, #variables.TodayClick#)

And the data display in the database as
PackageID:        ViewDate:          NumView
22                    8:34:17 AM               2
21                    8:34:17 AM               2
11                    8:34.17 AM               3
5                      8:34:17 AM               1

And all of these are happening at different times but yet they have the same time. Is there a certain format that access will only accept. Thanks in advance.
Who is Participating?
You're using the wrong code :)  

DateFormat() returns a string, not a datetime object.  Truthfully you should not use it to insert values into a datetime fields.   Yes, Access can convert some strings to datetime values correctly, but if the format is wrong you get results like :  6/15/1905.    

Second, you didn't put single quotes around #DateFormat(Now(), "mm/dd/yy")#, so I suspect CF is passing some kind of numeric value to the database.

10 / (divided by) 4 / (divided by) 2007 ... or
2007 - (minus) 10 - (minus) 4

Access will then try and convert the numeric value to a date/time. The result in this case is  6/15/1905. Using <cfqueryparam> or CreateODBCDate() avoids problems like this altogether.
In the field set the data format to "General Date". This should display as:

mm/dd/yyyy hh:mm:ss
> #DateFormat(Now(), "mm/dd/yy")#

That would only insert the date.  If you want to insert both the date and time, I suggest using either cfqueryparam or CreateODBCDateTime().   Its good to get into the habit of using cfqueryparam for all user entered values.

... ...,
<cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp">,
... etc..

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Karla77Author Commented:
I'm sorry I wasn't clear. I just want the date to display in the database, but it's not giving me the date it's just display time.
Another option is to set the default value for the column to now() in Access.  Then you won't have to insert anything. Access will automatically populate the field with the current date and time whenever a record is inserted.
Oops I didn't see your response before posting.  

> I just want the date to display in the database, but it's not giving me the date it's just display time.

You can change how Access displays the date as EMCIT mentioned (just use a different format).  But to be clear, that has no effect on how ColdFusion displays the information.  
In other words, if you just want to change how Access displays the values when you open your database, then use EMCIT's suggestion.  
Karla77Author Commented:
Ok. I change the format to short date and it change the dates to 12/30/1899. The dates should be today's date. Then I tried to run my code again and when I tried to view the date again they were back to just display the time. I have in my code for the dataformat to be mm/dd/yyyy. It's not store it like that, I guess.
> I have in my code for the dataformat to be mm/dd/yyyy. It's not store it like that, I guess
> (#url.Num#, #DateFormat(Now(), "mm/dd/yy")#, #variables.TodayClick#)

Sounds like your code is inserting the wrong value.  I notice there are no single quotes around #DateFormat()#. So CF may be inserting the wrong value.  

That's one of the reasons I usually using cfqueryparam.  You could also use CreateODBCDate()  to insert the date only.

Karla77Author Commented:
Ok. I created a query calling the table tblCount. When I <cfoutput> the query I got the date in this format: yyyy-mm-dd. So, I went back through my code and change all of the dateFormat to that. Then, I ran the code again and the date is displaying in the database but it's the wrong date. It's entering today's date as 6/15/1905.  If I do <cfoutput>#DateFormat(now(), "yyyy-mm-dd")#</cfoutput> it will display on the screen 2007-10-04.   I don't get.
If you still choose to use DateFormat(), you will need to put single quotes around it.

(#url.Num#, '#DateFormat(Now(), "yyyy-mm-dd")#', #variables.TodayClick#)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.