Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ERROR: invalid input syntax for type date: ""

Tags:

postgresql

I have the following psql query and can't understand why I get error ERROR: invalid input syntax for type date: "".

My query looks as follows:

SELECT count(*) FROM campaigns 
WHERE 
    dstart >= '2010-09-02' AND 
    dend <= '2010-09-02' AND 
    status != 'S' AND 
    status != 'C' AND 
    status != 'E' AND 
    (dsignoff <> '' AND dsignoff is not null) AND 
    (dstart <> '' AND dstart is not null) AND 
    (dend <> '' AND dend is not null) AND 
    clientid=20005294;

dstart,dend and dsignoff are all defined as date types.

like image 810
Elitmiar Avatar asked Sep 02 '10 11:09

Elitmiar


1 Answers

Since dstart,dend and dsignoff are defined as date, they can not be compared to string that represents invalid date (''). Try this:

SELECT count(*) FROM campaigns 
WHERE 
    dstart >= '2010-09-02' AND 
    dend <= '2010-09-02' AND 
    status != 'S' AND 
    status != 'C' AND 
    status != 'E' AND 
    (dsignoff is not null) AND 
    (dstart is not null) AND 
    (dend is not null) AND 
    clientid=20005294;
like image 158
Alex Reitbort Avatar answered Sep 18 '22 22:09

Alex Reitbort