Page 1 of 1
If hiredate is NULL, then..
Posted: Mon Oct 03, 2005 9:52 am
by 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.
Posted: Mon Oct 03, 2005 11:03 am
by 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
Re: If hiredate is NULL, then..
Posted: Mon Oct 03, 2005 11:48 am
by chulett
gateleys wrote: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.
Re: If hiredate is NULL, then..
Posted: Mon Oct 03, 2005 12:07 pm
by 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.
chulett wrote:gateleys wrote: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.
Posted: Mon Oct 03, 2005 12:25 pm
by 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
Posted: Mon Oct 03, 2005 12:35 pm
by gateleys
THanks ketfos, the char(128) works. I still haven't understood the reason behind it thogh.
ketfos wrote: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
Posted: Mon Oct 03, 2005 8:34 pm
by 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.