How to compare date without time with now() in PostgreSQL?
Dung Do Tien Dec 05 2021 101
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.
- C0
Corner Entertainment Dec 05 2021
Oh, you can convert
now()to date data type. To convert you can usecast()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.
- N0
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()::dateYou need to convert now() from timestamp to date.
* Type maximum 2000 characters.
* All comments have to wait approved before display.
* Please polite comment and respect questions and answers of others.