Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

First day of current year

Tags:

sql

postgresql

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?

like image 844
user1392203 Avatar asked Jun 18 '12 09:06

user1392203


People also ask

How do you determine the first day of the current month?

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.

How do I get current year and next year in SQL?

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));

How do I get last day of previous year in SQL?

SELECT DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0));


1 Answers

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
like image 80
beerbajay Avatar answered Oct 19 '22 02:10

beerbajay