I have the below query in a postgresql database
SELECT *
FROM accounts
where insertdate BETWEEN '2012-01-01' AND CURRENT_TIMESTAMP
So how can I replace the '2012-01-01'
asking for the first day of the current year
There is one more issue. When I m having a new record in the account table the same moment is running the above select so it doesnt bring me the record I have just made.Is it reasonable? What is the best way to overtake it?
1. First day of current month: select DATEADD(mm, DATEDIFF(m,0,GETDATE()),0): in this we have taken out the difference between the months from 0 to current date and then add the difference in 0 this will return the first day of current month. 2.
Just run these SQL queries one by one to get the specific element of your current date/time: Current year: SELECT date_part('year', (SELECT current_timestamp)); Current month: SELECT date_part('month', (SELECT current_timestamp)); Current day: SELECT date_part('day', (SELECT current_timestamp));
SELECT DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0));
You're looking for date_trunc()
, which can truncate a date to a specified precision (e.g. year
, month
, day
):
SELECT date_trunc('year', now());
In your query:
SELECT * FROM accounts where insertdate BETWEEN
date_trunc('year', now()) AND CURRENT_TIMESTAMP
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With