Zero Date

[pen81]

I have a simple problem (hopefully). I need to test for zero dates (0000-00-00) in my report. How can I do this using the select expert?

I tried Date(0000,00,00) but this seems to evaluate to {d '-4714-12-31'} in the final SQL produced by Crystal. I need the final SQL to simply be: = '0000-00-00'

I am using CR10 and MySQL

 

[PatriciaObreja]

Did you try in Select Expert to use not "equal to" option, but "formula:" and enter:
ToText({Orders.Order Date}) = '0000-00-00'

 

[pen81]

I have just tried that but it doesnt make a difference. Nothing appears in the SQL that Crystal produces, I am assuming this is because the formula ToText is evaluated at a later stage. However, the results are not as expected so for some reason this is not working as intended.

By default ToText returns the date in the format dd/mm/yyyy but testing for equality with 00/00/0000 doesnt work, ToText appears to return blank or null but I dont know how to test for this, using = "" does not work

 

[Madawc]

Exactly what is in these 'zero dates'? A lot of databases will use nulls, meaning you need to use IsNull and put it as your first command, because otherwise null stops the formula.

If it really is 0000-00-00, then test it first as a string with that value. Only then as a date with a real value. ToText and Date should take care of it.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[pen81]

Hi and thanks for your help. I have found a solution by setting the "Convert Database NULL values to default", found in File -> Report Options. With this set I can then use ToText({datefield}) = "" in the Select Expert.

When I use MySQL to query my database, the 'zero dates' as I call them display as 0000-00-00, but I'm guessing this is just MySQL displaying null fields - the database is populated from a CSV file that contains 0 as a value for the dates in question.