EasyCFM.COM ColdFusion Forums / MS Access Related Issues / inserting dates into access

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: inserting dates into access

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

asmostad
04-30-2006 @ 2:40 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 21
Joined: Nov 2005

okay, this is driving me crazy. i am trying to insert a date into a date/time field in my access db. i am passing a form variable from one page to the action page which inserts the data. if i make the form field hidden with a value of #int(Now())#, it will insert the correct date. just passing #Now()# displays incorrectly, and if i let users input the date it is displays incorrectly. even more interesting, is that diplaying the form field that is passed to the action page displays correctly, just not when displaying that row in the table. so it seems to me like the data being passed is correct, it just doesnt get displayed or entered into the talbe correctly.

anyone have any suggestions?

megan
04-30-2006 @ 3:18 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 2398
Joined: Jan 2003

hi

when inserting your date try using the CreateODBCDateTime() function e.g #CreateODBCDateTime(now())#

when displaying the date, you can use the DateFormat() and DateFormat(functions, e.g. #DateFormat(mydatetimevar, 'mm/dd/yyyy')# #TimeFormat(mydatetimevar, 'hh:mm tt')#

you can look up these functions at cfQuickDocs

hth ~megan

We have art so that we shall not die of reality ~ Nietzsche

weathermanfsu
10-04-2006 @ 1:57 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 183
Joined: Apr 2004

If you are trying to enter the date and/or time for right now into a field in the database, you do not need the # signs.  This is not a CF function, it is a SQL function in Access.  All you have to do is say
<cfquery...>
insert into mytable (mydate) values (now())
</cfquery>

Or use the update statement to do the same

update my_table set mydate = now() where...

Zano
01-21-2008 @ 6:41 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 3
Joined: Dec 2002

I am having a similar problem, & I finally figured out what was going on. I changed the tables field from a date to text and forced the date using  the value = #DateFormat(Now())#,#TimeFormat(Now())#. and it works correctly!

Many Thanks, Zano

This message was edited by Zano on 1-21-08 @ 9:06 PM

weathermanfsu
03-21-2008 @ 8:43 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 183
Joined: Apr 2004

I hope you understand that since you swapped the field you are storing he date in, into a text field, you will NOT be able to sort that data by date/time.  

You can easily store date/ time info in a date field in access using the createdate() function.  Most people use the wrong syntax in their sql and that is the problem, not the field type.  

Sounds like you got confused on when to use parens and when not to use them.

Also remember, if you are DISPLAYING dates then use dateformat(date, 'display mask') and you can format the date to look like you want it.  You do not format dates when inserting them into the database, you format when outputting for display.


Website Designed and Developed by Pablo Varando.