• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 745
  • Last Modified:

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">
INSERT INTO tblCount
(PackageID, ViewDate, NumView)
Values
(#url.Num#, #DateFormat(Now(), "mm/dd/yy")#, #variables.TodayClick#)
</cfquery>

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.
0
Karla77
Asked:
Karla77
  • 7
  • 3
1 Solution
 
EMCITCommented:
In the field set the data format to "General Date". This should display as:

mm/dd/yyyy hh:mm:ss
0
 
_agx_Commented:
> #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.

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


0
 
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
_agx_Commented:
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.
0
 
_agx_Commented:
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.  
0
 
_agx_Commented:
In other words, if you just want to change how Access displays the values when you open your database, then use EMCIT's suggestion.  
0
 
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.
0
 
_agx_Commented:
> 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.

0
 
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.
0
 
_agx_Commented:
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.

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

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.
0
 
_agx_Commented:
If you still choose to use DateFormat(), you will need to put single quotes around it.

...
Values
(#url.Num#, '#DateFormat(Now(), "yyyy-mm-dd")#', #variables.TodayClick#)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now