insert a blank date

[crabgrass]

I have a asp page that displays a series of dates and other data. The user is allowed to edit these dates and return the new dates to an edits table. If the date is not edited the app will write the original date to the edits table. I have a problem when the original date is blank. (not null but blank) The app errors with an invalid date in this instance. How can I insert a blank date into a table. The table format is VFP dbf. the command line is :

.fields("igadraft").value = request.form("igaDraft")

Thanks for any help on this one.

 

[Sheco]

If the database is storing this value using a character string then it could be blank.

If the database is storing the value as a date datatype then it can be null but it cannot be blank because a date datatype is actually a numeric type... it is a number representing the amount of time that has passed since a specific date known as the epoch date:[tt]
unix January 1, 1970
VMS November 17, 1858
mac January 1 1904
MSDOS January 1, 1980
MS SQL Server January 1, 1900
VBScript December 30, 1899
Oracle December 31st, 4713 BC
MySQL January 1, 1970[/tt]


Ultimately, you are probably more concerned with how the data is displayed rather than how it is stored.

 

[crabgrass]

Thanks for your reply.
You are correct in your analysis except that my database (MS Visual FoxPro) does in fact differentiate between a null, blank, and zero in a date field. A blank indicates the field has never been populated. My table is defined to not allow nulls.

vartype is 'date',
isblank()=.t.
isnull = .f.

Even so, if I were to redefine the table to allow a null, how would you insert it into the table?

 

[Sheco]

If you are desiging a new system I'd recommend using Null on date fields instead of FoxPro's empty or blank values because those others are not really supported on other systems. The SQL would be something like:
INSERT INTO MyTable (SomeField, MyDateField, OtherField)
VALUES (1, Null, 2)

or

UPDATE MyTable SET MyDateField = Null WHERE 1 = 2 [red]*[/red]


[red]*[/red] assume you'd use some reasonable where condition


More likely however this is not a new system but rather a new web front-end for an old system. If it was a new system you'd probably rather use a different database platform. If this is your situation then changing the field definitions could break other installed FoxPro software that uses the same database so Null isnt really an option in this situation. See here for a tek-tips thread on the syntax of setting Date fields to blank: thread184-1297272

 

[crabgrass]

Thanks