How to compare dates from two tables with blanks values
I have a situation where I need to update dates in a primary table from
regular imports of data, eg: this is the base select query
select d.row_id,
fname||lname,'joineddate',d.joineddate,'joineddate',s.joineddate,0 as bool1
from import s join members d on d.contact_id=s.contact_id where
cast(nullif(d.joineddate,NULL) as timestamp) !=
cast(nullif(s.joineddate,'') as timestamp)
This gives zero records, however I cant seem to get a query that works.
For non-date fields I just use
Coalesce(fieldprime,'')!=coalesce(fieldiimport,'') which works fine but
chokes on dates where there is a blank value
thanks in advance
Le 19/12/2018 à 11:41, Mike Martin a écrit :
cast(nullif(d.joineddate,NULL) as timestamp) != cast(nullif(s.joineddate,'') as timestamp)
Try with
d.joineddate IS DISTINCT FROM s.joineddate
https://www.postgresql.org/docs/current/functions-comparison.html
Cheers
--
Arnaud
thanks for this. I did get it to work using coalesce and nullif on opposite
sides of the where condition, but the IS DISTINCT FROM certainly sounds a
better approach. Coming from SQLServer until last year , never came across
it before
Mike
On Wed, 19 Dec 2018 at 10:57, Arnaud L. <arnaud.listes@codata.eu> wrote:
Show quoted text
Le 19/12/2018 à 11:41, Mike Martin a écrit :
cast(nullif(d.joineddate,NULL) as timestamp) !=
cast(nullif(s.joineddate,'') as timestamp)
Try with
d.joineddate IS DISTINCT FROM s.joineddatehttps://www.postgresql.org/docs/current/functions-comparison.html
Cheers
--
Arnaud