If hiredate is NULL, then..

[gateleys]

Hi,
I have 2 dates, graduate_date and hiredate. I need to find the number of days between the two. However, if the hiredate is NULL, I need to set the numOfDays to 100, else Difference in days between the 2 dates.

Everything works, except that the NULL condition in the hiredate doesn't seem to be checked. I have a stage variable SVDays which is assigned -

Code: Select all

if IsNull(DSLink2.hiredate) then 100 else DateYearFirstDiff(DSLink2.hiredate, DSLink2.graduatedate)

Even with this, when the hiredate is NULL, I am getting the difference as 0, and not 100 as specified.

[ketfos]

Hi,
I would suggest you to try as

if (DSLink2.hiredate) = char(128) then 100 else DateYearFirstDiff(DSLink2.hiredate, DSLink2.graduatedate)

Note - The null value is represented externally, as required, by a character string consisting of the single byte Char(128). At run time it is assigned a data type of null.


Ketfos

[chulett]

Even with this, when the hiredate is NULL, I am getting the difference as 0, and not 100 as specified.

Point of fact - your hiredate is not null or the check would in fact succeed. Question would be what is the source of this data - flat file, database, what?

Probably better to do something more generic that would catch a null or an empty string:

Code: Select all

if Len(Trim(DSLink2.hiredate)) = 0 then 100 else DateYearFirstDiff(DSLink2.hiredate, DSLink2.graduatedate)

is one way.

[gateleys]

The source is ODBC pointing to SQL Server 2000, and the data type for hiredate is Timestamp. The SQL Server does show NULL in some records for the hiredate field. However, the check that I specified seems to fail to capture that, and takes the else path.

Even with this, when the hiredate is NULL, I am getting the difference as 0, and not 100 as specified.

Point of fact - your hiredate is not null or the check would in fact succeed. Question would be what is the source of this data - flat file, database, what?

Probably better to do something more generic that would catch a null or an empty string:

Code: Select all

if Len(Trim(DSLink2.hiredate)) = 0 then 100 else DateYearFirstDiff(DSLink2.hiredate, DSLink2.graduatedate)

is one way.

[ketfos]

Hi,
Did you try using the char(128) as suggested earlier.

There is another way as follows - if that does not capture the null data

if (Trim(DSLink2.hiredate) < char(33) or Trim(DSLink2.hiredate) > char(127) ) Then 100 else DateYearFirstDiff(DSLink2.hiredate, DSLink2.graduatedate)

This will take care of unprintable characters in you data file.

Ketfos

[gateleys]

THanks ketfos, the char(128) works. I still haven't understood the reason behind it thogh.

Hi,
Did you try using the char(128) as suggested earlier.

There is another way as follows - if that does not capture the null data

if (Trim(DSLink2.hiredate) < char(33) or Trim(DSLink2.hiredate) > char(127) ) Then 100 else DateYearFirstDiff(DSLink2.hiredate, DSLink2.graduatedate)

This will take care of unprintable characters in you data file.

Ketfos

[ray.wurlod]

The system variable @NULL.STR is the "string representation of null" using which would avoid needing to evaluate Char(128) in every row processed.