ODBiC Discussion Board

RE: English date problem persists, Jongr, 03-26-2007

I'd need to see exactly what you are trying to do

by Roger Harris, April 1, 2007 22:02

The SETOPTION DATE command does not have any effect on database date formats. Rather, it tells ODBscript what format to *expect* for dates whenever any of the special date functions are used, such as when you use the $format( ) function to get a specific date format for output or insertion into a database.

You can work with dates in any format as input and get whatever format you want for output but you need to know exactly what format dates will be in when they are input by a user or selected from a database, and you need to understand exactly how ODBscript deals with them when a date variable is used in a function.

So first, are you trying to enter dates from a form into a database, or are you trying to display dates that are already in the database? Likely, you need to do both, but keep in mind that the two reformatting situations are reversed, so they need to be handled differently. In either case, you need to know exactly how the dates are stored in the database. It sounds like your database is using month-day-year order but you want users to enter and view dates in day-month-year order. (If you could get your database to directly use month-day-year order, which is possible with "localization", then there wouldn't be any problems and you wouldn't need to do any date reformatting at all with ODBscript.) Otherwise, if you are forced to use the database as it is, you need to reformat all dates, both when you are inserting them into the database and when you are displaying them from a database.

In short, you need to use a format "mask" in the $format(mask, date) function that has the date order that you need for either the Web page output or the SQL INSERT statement, but before using that function, use SETOPTION DATE to say what format the date varable used in the function will have.

Using the function $format("dd-Mmm-yyyy', $someDate$) will output a date in the format that you want, but it will only work properly if the variable $someDate$ has the expected format -- whatever that is. If your database dates are in month-day-year order, then that is the default expectation in ODBscript, so you don't need to use SETOPTION DATE="mdy" although it wouldn't hurt to do so. When you display the dates, just use $format("dd-Mmm-yyyy', $databaseDate$) to reformat them to the order you want. If you are inserting dates into the database, however, the operation is the reverse. That is, if user will enter dates in day-month-year order, then you need to use the SETOPTION DATE="dmy" to tell ODBscript to expect that order. Then in your SQL INSERT statement, you need to reformat to put the dates in the order required by the database, for example (using the special # delimiters that MS Access wants for date fields), #$format("mm/dd/yyyy", $inputDate$)#.


Post Your Reply:

E-mail  optional

HTTP Link: 
Link text: 


Copyright ©1997-2003, Roger Harris. All rights reserved.