how to handle null in date field of postgresql
Solution 1
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.
Solution 2
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.
Pallavi Singh
Updated on June 18, 2022Comments
-
Pallavi Singh almost 2 years
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"