date 1

[GUJUm0deL]

Hey folks, this is what i'm trying to do:
I have a form, when the user submits the form and all the fields get written to the dB, I also want the date the form was submitted written to the dB as well, and ideas??
Thanks folks... I have not failed; I merely found 100,000 different ways of not succeding...

 

[webmigit]

Add a date field to the db is step 1 of course... and then in your sql the value is something like:

#DateFormat(Now(),"mm/dd/yyyy")#

 

[jackied]

INSERT INTO MyTable
(MyDate, ... )
VALUES({ d '#DateFormat(Now(),"yyyy-mm-dd&quot#' }, ...)

would be safer.

This way there can be no confusion between month and day.

 

[webmigit]

I've never had any confusion problems, just remember to make the db field's type date and it works fine.

 

[r937]

may i make a suggestion? forget about changing your INSERT code, and just declare the column with a DEFAULT clause for the current date -- voila, c'est tout, and bob's your uncle

(unless it's mysql, then you're s.o.l.)

rudy

http://rudy.ca/

 

[GUJUm0deL]

webmidgit, Hi. When you said to "and then in your sql the value is something like:

#DateFormat(Now(),"mm/dd/yyyy&quot# ", what exactly goes in the SQL query??
Can I just add #DateFormat(Now(),"mm/dd/yyyy&quot# to the form?? Or does the quesry need something??
Thanks. I have not failed; I merely found 100,000 different ways of not succeding...

 

[webmigit]

You'll want to add that to the sql.. Did I help?
Vote!

 

[GUJUm0deL]

What exactly do I add to the SQL?? And is that only place where I refrence that??
Thanks... I have not failed; I merely found 100,000 different ways of not succeding...

 

[r937]

may i make a suggestion? forget about changing your INSERT code, and just declare the column with a DEFAULT clause for the current date

rudy

 

[GUJUm0deL]

r937, how would I do that??
Where do I declare the column with the DEFAULT clause for the current date?? I have not failed; I merely found 100,000 different ways of not succeding...

 

[r937]

in the CREATE TABLE statement, although at this point you would probably want to use ALTER TABLE, unless it's mysql or access

syntax may vary slightly with your database -- you never said which one

as i mentioned earlier, if it's mysql (unlikely, given that most people using mysql use php) you're s.o.l.

if it's access, open the table in design view, and put Now() in the line for Default Value

then leave the date field out of your INSERT statement altogether -- don't mention it in the column list, and don't provide a value in the value list

rudy

 

[GUJUm0deL]

r937, Ok it worked...
You get a nice shiny purple-ish star... I have not failed; I merely found 100,000 different ways of not succeding...

 

[GUJUm0deL]

r937, quick question: if my boss wanted the date the form was filled out to be printed in a status page, can I do that?? I have not failed; I merely found 100,000 different ways of not succeding...

 

[r937]

well, sure, now just write a SELECT statement for it

columns that have a DEFAULT attribute are no different from other columns, except they have a DEFAULT attribute, which means you don't have to supply a value when inserting a row, assuming you're happy with that row getting the default value in that column

you could still insert a row with some other value, in which case the default value would not be used

rudy

 

[GUJUm0deL]

Hey, intersting result I get: I did the query, where I selected date from my table, and then I did the output, no problemo there...
Problemo on how the date is displayed this is what gets displyed 2002-09-12 13:12:042002-09-12 13:21:46
Now in the dB, under DESIGN VIEW, the fieldname DATE and the datatype is set to Date/Time, then in the bottom under FORMAT I have 'short date' (should be saved as 09/12/02)...and the DEFAULT VALUE is now()
And even in the DATASHEET VEIW, the date field's record is saved as 09/12/02, so how come when its displayed on the page it appears as: 2002-09-12 13:12:042002-09-12 13:21:46
Thanks... I have not failed; I merely found 100,000 different ways of not succeding...

 

[r937]

date/time values are actually stored as integers (not just msaccess, but all databases, with the possible exception of mysql)

i.e. dates are always converted from date strings to integers on the way into the database, and from integers to date strings on the way out

if you run a SELECT on the date field as a date field, i think what happens is that msaccess passes it over as an odbc date field, whatever format that is, and then cold fusion recognizes that it's a date and formats according to its default rules -- i think

i always avoid any doubt by controlling the way the dates are pulled out of msaccess in the SELECT, using the Format() function, which will return a date string to coldfusion

rudy

 

[GUJUm0deL]

r937, I got it...I fixed the date problem...wanna know how?? Of course you do, lol...
This is what I did on the action page: #DateFormat(date, "MM/DD/YY&quot#, then in the query, I added a WHERE clause saying: conf_number_ID = '#URL.conf_number_ID#' (since my conf_number_id is not working right, I manualy add in a number)
And lo-behold...it works... I have not failed; I merely found 100,000 different ways of not succeding...