Problem with "Where" clause

[#1]

Hello everyone

I have the following code that gives me a problem with the "Where" clause:

SQL SELECT "job_nummer",

    jobnr,

    "datum_auftrag",

    Year ("datum_auftrag") as Jahr,

    Month ("datum_auftrag") as Monat

FROM EASY.job where "datum_auftrag" = date;

With the "Where" clause I would like to exclude entries that do not have a date.

Could someone help me with this?

Thank you very much in advance...

[#2]

From EASY.job where "datum_auftrag" IS NOT NULL AND "datum_auftrag" = Date;

?

[eduardo_sommer]

What brand of SQL are you using. If it is Microsoft SQL server. it's Transact-SQL has a function called ISDATE, which you can use to check if your field contains a valid date.

Eduardo

[Anonymous]

It cannot be tricky...  Maybe there are NULLs and BLANKs there (?).  Try this

where ltrim(rtrim("datum_auftrag")) <> '' and "datum_auftrag" is not null

[#5]

Hi Eduardo?

How does the ISDATE funktion work?

Best

Jan

[eduardo_sommer]

Where ISDATE("datum_auftrag") = 1

If ISDATE returns 1, the date is valid.

Eduardo

[#7]

It is an odbc database is guess it does not have this funktion.

[eduardo_sommer]

Hi Jan,

ODBC is just the link between your application and the database. It is not "the database". So, depending on the underlying database, you will have different command sets.

Eduardo

[#9]

ok, my apologies. it is a pervasive database. plaese excuse my mistake. this is quite new to me...

[#10]

FROM EASY.job Where "datum_auftrag" is null;

works. it gives me the empy fields.

FROM EASY.job Where "datum_auftrag" is not null;

works too, but it does not filter out the empty fiels, they are still there...

[eduardo_sommer]

Hi

I made a quick research on the IsDate function for Pervasive. It works a little different from T-SQL. Instead of returning 1 for true and 0 for false it just returns true or false.

So, please try using

     Where ISDATE(datum_auftrag)

without the '= 1' I suggested before.

Eduardo