How to compare date without time with now() in PostgreSQL?

Dung Do Tien Dec 05 2021 18

Hello guys, I have a small PostgreSQL with two tables dealerpromosfile and dealerpromosmapper. In dealerpromosfile table has two columns are startdate and enddate (both of them have date datatype). And now I want to get all promos still not expire.  You can see the query below:

SELECT DISTINCT  
    a.cashdiscount, a.brandid, a.downpayment
    case when a.price > 0 then 1 else 0 end as price_orderby,
    case when dp.dealershipsid > 0 then 1 else 0 end as dealership_orderby
FROM public.dealerpromosfile a 
LEFT JOIN public.dealerpromosmapper dp ON dp.dealerpromosid = a.id
WHERE a.ispublish = TRUE 
AND a.startdate <= now() AND a.enddate >= now()

I compare startdate and enddate with now() method but this method return timestamp datatype. It works well for me but has a problem, It does not return promos when enddate or startdate equal with the current date. 

I know their different data types, it compares both dates & times. But I have no idea to solve it.

So how can I compare date without time with now() function in PostgreSQL?

Thanks for any suggestions!

Have 2 answer(s) found.
  • C

    Corner Entertainment Dec 05 2021

    Oh, you can convert now() to date data type. To convert you can use cast() function. For example: cast(now() as date).

    Full solution for you:

    SELECT DISTINCT  
        a.cashdiscount, a.brandid, a.downpayment
        case when a.price > 0 then 1 else 0 end as price_orderby,
        case when dp.dealershipsid > 0 then 1 else 0 end as dealership_orderby
    FROM public.dealerpromosfile a 
    LEFT JOIN public.dealerpromosmapper dp ON dp.dealerpromosid = a.id
    WHERE a.ispublish = TRUE 
    AND a.startdate <= cast(now() as date) AND a.enddate >= cast(now() as date)

    I hope it helpful for you.

  • N

    Nguyễn TrươngAnh Tuấn Dec 05 2021

    You can try this solution

    SELECT DISTINCT  
        a.cashdiscount, a.brandid, a.downpayment
        case when a.price > 0 then 1 else 0 end as price_orderby,
        case when dp.dealershipsid > 0 then 1 else 0 end as dealership_orderby
    FROM public.dealerpromosfile a 
    LEFT JOIN public.dealerpromosmapper dp ON dp.dealerpromosid = a.id
    WHERE a.ispublish = TRUE 
    AND a.startdate <= now()::date AND a.enddate >= now()::date

    You need to convert now() from timestamp to date.

Leave An Answer
* NOTE: You need Login before leave an answer

* Type maximum 2000 characters.

* All comments have to wait approved before display.

* Please polite comment and respect questions and answers of others.

Popular Tips

X Close