reset date

[AlastairOz]

I am trying to reset the date in a field back to default, a blank date. (remove the date)

REPLACE dateField WITH ""

does not work.
Can anybody help

 

[Mike Lewis]

Alastair,

A couple of possibilities:

BLANK FIELDS DateField

or

REPLACE DateField WITH {}

In general, {} is the expression of a blank date.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

As an explanation: If the date field is a date field, it doesn't accept strings. VFP does no automatic type conversions like SQL Server does, eg by accepting strings like 'YYYMMDD'. Take a look at the help on date functions and date literals.

Besides that since VFP3 we're in the age of NULLable fields, if you really want a blank date field, rather use NULL than an empty date. Only foxpro does know such a thing as an empty date or datetime, in fact SQL Server only knows a pure date without a time portion since SQL 2008.

The empty dates will hurt you, if you ever migrate to another database. It's easy to convert them, but you'll need to rethink code working with empty dates. It' tempting to use empty dates, as you can compare with an empty date, subtract an empty date from a real date and do other computations and expressions all making good sense.

But in other databases you either work with some ultimo date - eg 1/1/1900 - or you work with NULL, wich has a few problems in expressions, eg date1=date2 will not result in .T. or .F. with one of them being a NULL date, but it yields NULL which can break code to work correctly. Some code will work nevertheless, eg NULL in a condition of an IF statement will mak VFP run the else statement, which may be okay in most cases.

The point is, if you want compatibility with other databases use NULL and then pay attention to the NVL() function to care for NULL values, which variables or fields could have and substitute NULLs with a reasonable value of the vartype, eg with {}, the empty date, for dates.

Eg you check IF NVL(datefield1,{})>NVL(datefield2,{}), you add strings via 'stringliteral'+NVL(charfield,'') etc.

Also use ISNULL() to check for NULL, don't use the expression (IF) var = .NULL., this will always be .NULL.

Limit NULLable fields to those whole really can be NULL, because you will not want to put all code with NVL(). IF you enforce some fields to contain a value, eg first name and last name fields, then you don't need to consider NULL values.

NULL is valuable nevertheless, as it's really meant for the absence of any value.

Bye, Olaf.

 

[Mike Lewis]

The empty dates will hurt you, if you ever migrate to another database.

You can say that again. Dealing with Foxpro-style blank dates is usually the biggest problem I face when moving VFP apps to a client-server back end (and not just SQL Server).

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[AlastairOz]

Hi Olaf,
I appreciate the very detailed answer. I will take you advise an go through my app and make the changes.
Does the issue with blank date fields extend to other types as well? Like Char fields?

Regards
Alastair

 

[Olaf Doschke]

Well, empty char fields are empty strings, this is available elsewhere too, so yes, empty dates are a special case.

But NULL as a placeholder for the absence of any value can be applied to any type of values.

For example a temperature of 0°F (or °C) is a temperature, while NULL means no temperature has been measured, that can be a big difference, for example for patients, if a database stores measured body temperatures .

You should think about what fields should be nullable at all, as it is a problematic value for expressions including NULL values.

Bye, Olaf.