Date Format Help

[pwinters]

I'm using MS Access 97 db with SQL & Java web front-end.

I figured out how to convert the date to a readable format when I display it on the page, but I can't figure out how to get the date formatted to update the table.

Here's my code...
String sql=
"UPDATE " +
"Tables_Status" +
" SET Tables_Status.TBL_LOE=" + tloe +
", Tables_Status.TBL_DD_TD= Format('tddtd', 'MM/DD/YYYY')" +
", ....

Currently, when I run this code I am getting an error of Too Few Parameters. Expected 1.
But, I think there's more to it than that.

Oh, tddtd is a String that I'm getting from the previous page when the user enters the date in this field (10/02/2002).
String tddtd = request.getParameter("tblddtd"

Also, when I take out the Format function, the date is stored as 12/30/1899.

Please help me figure this out...it's driving me nuts!

Thanks

 

[r937]

the 12/30/1899 date is easy to explain, it means the date probably got entered as a zero, which is what you get when you divide 10 by 02, and then divide the result by 2002

access wants you to submit date literals surrounded by hashes

you will have no problems distinguishing between october 2 and february 10 if you enter dates in iso format, i.e. #2002-10-02#

rudy

http://rudy.ca/

 

[pwinters]

r937,

Thanks for your response, but it doesn't seem to be working.

I changed my code to...
", Tables_Status.TBL_DD_TD= Format(#tddtd#, 'MM/DD/YYYY')" +

and the error I get is:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access 97 Driver] Syntax error in date in query expression 'Format(#tddtd#, 'MM/DD/YYYY')'.

I don't think the # sign is excepted in this expression, or maybe I didn't get the syntax right(??).

Any other thoughts?

Thanks

 

[r937]

since TBL_DD_TD is a datetime column, then you must set it using a valid access datetime string

the FORMAT expression is obviously not correct, as it does not supply the hashes

i do not know jsp but you could try plugging tddtd into the sql statement like this --

"SET ...
, Tables_Status.TBL_DD_TD= #" + tddtd + "#"

i cannot remember if access defaults to mm/dd/yyyy or dd/mm/yyyy whenever dd is <= 12, which is why i never feed it anything except yyyy-mm-dd strings

rudy

 

[pwinters]

Thanks for your help Rudy!

It's working! I had to modify your suggestion a little bit, but I got it working (FINALLY!) Thanks so much.

Here's my modified code in case anyone else comes across this problem:
&quot;UPDATE &quot; +
&quot;Tables_Status&quot; +
&quot;Set&quot; +
&quot;Tables_Status.TBL_DD_TD=
Format(#&quot;+ tddtd +&quot;#, 'MM/DD/YYYY')&quot; +
....


I do have a followup question though...

Any idea how to allow the user to leave this field blank if no date is needed. Currently, if I leave the field blank, I receive an error that says:
Syntax error in date in query expression 'Format(##, 'MM/DD/YYYY')'

Thanks again!

 

[r937]

as i said, i don't know jsp

i would imagine some kind of conditional test in jsp to see if the field is blank, and if not, then conditionally generate that part of the UPDATE statement

String sql=
&quot;UPDATE Tables_Status&quot; +
&quot; SET Tables_Status.TBL_LOE=&quot; + tloe

IF tddtd <> &quot;&quot;
THEN sql = sql +
&quot;, Tables_Status.TBL_DD_TD= #&quot; + tddtd + &quot;#&quot;

i still don't understad why you're using the FORMAT function -- you said tddtd is a string, so just stick it between hashes and you're done

rudy

 

[pwinters]

Thanks again for your help.

I tried the if statement, but it produces an error
Can't convert java.lang.String to boolean. if (tefdate=&quot;&quot

And if I try if (tefdate<>&quot;&quot It says Missing term.

As for the Format Function...Maybe it's the way Jsp handles formatting dates, but I can't get it towork w/out the format function, unlike asp.

Thanks

 

[r937]

like i said, i don't know jsp, you'll have to look up the syntax of the IF statemet yourself

as for the format, i was sure from the way you had it that it was part of the sql, not part of jsp, but if it works, hey, go with it...