Setting Date Variable to Null

This is a simple technique and I’m sure most of you might be already aware of this. But I thought of posting it here as I receive queries regarding the same from colleagues and online forums.

There might be situations where you need to assign a null value to a date field. But if you try assigning the null value directly, you will get a compile error from the PeopleCode editor. Unlike other variables, you are not supposed to assign a null variable directly for a date variable.

Now the question is, how will you then make the variable null. The answer is simple. You can use the built-in function Date() to assign a null value to the date variable. See the example below.

Local Date &dMyDate;

Rem Assign the variable &dMyDate with Null Value;

&dMyDate = Date(0);

So far so good. The above technique will work only if you are concerned with PeopleCode. Now let us think of other scenarios such as sqls from PeopleCode, application engines etc. The use case you wanted is to insert a null variable to the database. You can do it very well if you use the syntax supported by the database. But what if you are not sure about the database? Are you planning for a switch in database?

Well in such cases, you can rely on the metasql’s delivered by PeopleSoft. You have one such metasql to insert null values. For this use case you can very well use the metasql %DateNull.