Karla77
asked on
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.
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.
> #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_timestam
... etc..
)
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_timestam
... etc..
)
ASKER
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.
> 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.
ASKER
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.
> (#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.
ASKER
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(
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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#)
...
Values
(#url.Num#, '#DateFormat(Now(), "yyyy-mm-dd")#', #variables.TodayClick#)
mm/dd/yyyy hh:mm:ss