How to compare date without time with now() in PostgreSQL?
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!
- 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()::date
You 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.