CODEWITHSUNDEEP
sqlpostgresqldateselectnull
Pallavi Singh
Pallavi Singh

Reputation: 175

how to handle null in date field of postgresql

I have to ignore rows which have null in date column. How can I do it?

select s."SR Closed Date"::date,
       s."Service Request Number",
       a."Activity ID" 
from sr_data_master s,
     activity_master a
where s."Service Request Number" = a."Service Request Number"
and a."Service Request Number" is not null 
and a."Service Tag" is not null 
and a."Activity ID" is not null
and s."SR Closed Date"::date is not NULL
group by s."Service Request Number",
         s."SR Closed Date"::date,
         a."Activity ID";

I am getting the error: 

 invalid input syntax for type date: "NULL"

Upvotes: 7

Views: 36648

Answers (2)

user330315
user330315

Reputation:

The error message indicates that your "date" column (which apparently isn't actually a date column) contains the string constant 'NULL' rather than a real null value (because there is no problem casting a real null value to date value).

You could use the following condition to exclude those rows:

and s."SR Closed Date" <> 'NULL'

But the correct fix for this problem is to store date values in DATE columns. Then you don't need the casting and you can't store invalid date values to begin with.

Upvotes: 2

Mureinik
Mureinik

Reputation: 311853

null is a valid value for a date. From the error message, it seems as though you have the string 'null' stored in that column. You could either check for it explicitly:

LOWER(s."SR Closed Date") <> 'null'

Or just remove the casting to date from the group by clause.

Upvotes: 1

Related Questions