OLE DB date filter

Try the following:

BENEFIT.STOP-DATE = 00000000

There should be enough zeros for a 2 digit month, 2 digit day and a 4 digit year.

Good luck!

FireGeek

Lawson stores the null dates in the format of 1/1/1700. The format should be the same as this date.

I have found queries to work when I specify a null date in this format.

1/1/1700 is only for some databases; others use 1/1/1800 and other flavors.

Regardless, when using OLEDB against Lawson, you are going thru IOS/DME/Data, etc. Not directly to database, so =00000000 should work as well as equal sign without anything. =

It is true Lawson stores null dates as 01/01/1700 however how you identify nulls in Crystal are different based on your connection and where you are identifying them.

Here is what I found if you are looking for NULL in a field use the following:

OLE:
Filter - BENEFIT.STOP-DATE = 00000000
Formula - isnull({BENEFIT.STOP-DATE})

ODBC:
Formula - Date(BENEFIT.STOP-DATE) = Date(1700, 01, 01)

Hope this helps.

FireGeek

John, thanks for this bit of information, "1/1/1700 is only for some databases; others use 1/1/1800 and other flavors." I thought 1800 was a typo!

FireGeek

Posted By FireGeek on 07/29/2010 01:34 PM
It is true Lawson stores null dates as 01/01/1700 however how you identify nulls in Crystal are different based on your connection and where you are identifying them.

Here is what I found if you are looking for NULL in a field use the following:

OLE:
Filter - BENEFIT.STOP-DATE = 00000000
Formula - isnull({BENEFIT.STOP-DATE})

ODBC:
Formula - Date(BENEFIT.STOP-DATE) = Date(1700, 01, 01)

Hope this helps.

FireGeek

The original question was how to filter on null dates using Lawson OLE DB, which would be part of the command string processed by Lawson -BEFORE- it gets to Crystal.

Posted By FireGeek on 07/29/2010 01:36 PM
John, thanks for this bit of information, "1/1/1700 is only for some databases; others use 1/1/1800 and other flavors." I thought 1800 was a typo!

FireGeek

https://www.lawsonguru.co...tore-NULL-Dates.aspx