Saturday, June 9, 2012

How to insert null value into date field in peoplesoft using SQLEXEC



If we build a record in peoplesoft App designer, other than date fields, all other fields will be 'not null' fields in database. only date columns will be null fields. 

But if we want to insert a null value using SQLEXEC as below.

&name='Samba';
&birthdate=null;    /* or &birthdate=' '; */
SQLexec("INSERT INTO TABLE VALUES(:1,:2)",&name,&birthdate);

Result in Database will be as below:

Name        Birthdate
Samba      01/01/1900

Thought we inserted a null value or space (' '), it has taken 01/01/1900 into the date field.

To avoid the above situation, we can try this.

&name='Samba';
&birthdate=Date(0);
SQLexec("INSERT INTO TABLE VALUES(:1,:2)",&name,&birthdate);

now the output will be

Name        Birthdate
Samba        Null

So Date(0) can be used to insert null value into date fields.Hope this will be useful.