Replace date field with none

[ameedoo3000]

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?

 

[Scott24x7]

REPLACE TABLENAME.DATEFILED WITH {//}

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

REPLACE ALL TABLENAME.DATEFIELD WITH {//}


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

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

 

[ameedoo3000]

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

Greetings
Ahmed

 

[Scott24x7]

You are very welcome.

Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

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

 

[Tore Bleken]

You can also use Blank, like Blank Fields Datefield.

 

[ameedoo3000]

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

Greetings to all of you.
Ahmed.

 

[Olaf Doschke]

Just stating the obvious, to replace with NULL you REPLACE DATEFIELD WITH [highlight #FCE94F].NULL.[/highlight] 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 [ignore]{^}[/ignore] and universal empty datetime is [ignore]{^:}[/ignore]. 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.

 

[ameedoo3000]

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.

Greetings
Ahmed