MySql default date 0000-00-00

[BiJae]

I have a MySql program that is putting the default value of "0000-00-00" in a date field that is not populated by the user. I'm in the process of rebuilding several dozen reports off this table. In a similar situation I was able to run a query to make all default values NULL. However this table is much larger and drives many modules of the application so this is not an option until I finish the new GUI and reporting system.

In the mean time I need to build the reports using the default date format. However, when I enter select on that field with the value of "0000-00-00" I receive the message, "A Date Is Required Here." I've tried using a formula but receive the same error message.

Has some one experienced similar? Do you know of a work around? I appreciate any suggestions.

Thank you

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart

 

[synapsevampire]

What does: when I enter select on that field with the value of "0000-00-00" I receive the message, "A Date Is Required Here." mean?

What is entering select?

Consider using a SQL Expression to CAST/CONVERT the field, if that's available in your version of MySQL, not posting software versions is a bad idea.

You might also create a View against the table and convert it within that.

I still have no idea what your difficulty is with the format of the field, try clarifying the problem.

-k

 

[BiJae]

My apologies for the confusion. Let me see if I can explain this a little better.

I'm using the select expert to pull data from a date field. The fields that have a value in the canceldate field I want to exclude. The ones that I want to include have the MySql default value of "0000-00-00". Using the select expert I've tried to select only those records with that date value. However, Crystal Reports 11, gives me the error: "A date is required here" when I use 'is equal to'.

I've tried to use 'is greater than' with today's date, however it reports nothing when run with that.

In a perfect world I could convert these values to NULL, however, that breaks the GUI that the users build. During this conversion phase I'm caught not able to build a report that gives me accurate information, i.e. only the items that do not have a cancel date.

Let me know if this makes more sense.

Thank you,

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart

 

[synapsevampire]

You can convert them in a SQL Expression or within a View as the data source.

Try posting the actual data types, and what you are specificially palcing in the select expert.

I would suggest skipping the select expert and use Report->Selection Formulas->Record and manually placing in the criteria.

But you need to know your data types, saying what's in the field is useful, however they may be storing dates as strings, not in a date type.

I doesn't make sensse that you can use is greater than but not equal to if all other thigns are identical.

Try using something like:

{table.date} < cdate(1950,1,1)

This should identify the rows that are out of scope, not sure what MySQL has as it's default value.

-k