Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Replace date field with none

Replace date field with none

Replace date field with none

Welcome everyone.
I would like to know what the code is in order to replace the date field with a null value or empty value?

RE: Replace date field with none


Or for the whole table (Be careful with this, it replaces all of them)


Best Regards,

"Everything should be made as simple as possible, and no simpler."hammer

RE: Replace date field with none

Your answer has achieved my goal.
thank you very very much Dear Scott24x7.


RE: Replace date field with none

You are very welcome.

Best Regards,

"Everything should be made as simple as possible, and no simpler."hammer

RE: Replace date field with none

You can also use Blank, like Blank Fields Datefield.

RE: Replace date field with none

thank you very much all.
I really benefited and learned a lot from you. I am proud of this.

Greetings to all of you.

RE: Replace date field with none

Just stating the obvious, to replace with NULL you REPLACE DATEFIELD WITH .NULL. IN TABLENAME or SQL-Update UPDATE TABLENAME SET DATEFIELD = .NULL. WHERE ID=1, so don't be afraid assignment of NULL needs anything special, it doesn't. Just beause comparison with NULL needs ISNULL(), assigment doesn't need any special method. Assignment and comparison are two different things, though they both use =.

The big difference of REPLACE vs UPDATE-SQL is, UPDATE-SQL by default works on all records and you need a WHERE clause to limit which records to update, whereas REPLACE by default only works on the current row and of course is specific to VFPs current anything principle - current directory, current database, current workarea, corrent record pointer - current record of current workarea most important here.

And in regard of empty vs NULL, any other DB will not know equivalents of empty dates, though depending on the database there are concepts like an earliest date and T-SQL indeed also accepts 0 and converts it to 1900-01-01, though you can go back to 1753. MySQL accepts an empty string as date, but all databases allow NULLable types and so that rather is the norm than empty dates.

Scotts {//} only works in British or English locales, if you're elsewhere in the world date separators are dots and can also be dashes. A universal empty date is {^} and universal empty datetime is {^:}. The disadvantages of these date/time literals is they are only allowed in SET STRCICTDATE TO 0 mode, in the least strict date mode. Nevertheless I use that setting and prefer it. What works with STRICTDATE 1 is CTOD('') and CTOT(''), so converting empty string to a date becomes the empty date, logical and easy to remember. With SET STRICTDATE TO 2 you'll get an error warning message even with that way of creating the empty date.

Last not least notice some negative behaviour of empty dates (SET STRICTDATE TO 0 to be able to use this syntax, though the behaviour doesn't depend on it):


? DATE()={^}, DATE()-{^} 

Though a date is not equal to the empty date, and though the empty date sorts before any other date in ascending order, DATE()-empty is 0 and not a high number, which it should be considering it sorts earlier than any other previous date.

I prefer NULL (or .NULL.), not only in regard to dates, though it also asks some caution using the NVL() function, if necessary to avoid the whole expression becoming NULL just because one operand in the term is NULL, NVL() is good to know anyway in situations OUTER JOINS create NULL values in result set fields of queries.

Bye, Olaf.

RE: Replace date field with none

Thank you very much Mr. Olaf.
Really important, good, comprehensive and useful additions.
Thank you all for all this valuable information and I owe you all the thanks and appreciation.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close