Current week number of the current month

Question

I used the following query to get the current week

select extract(week from current_timestamp) and it shows 34 , which is great.

But how do I get the current week number of the current month.

Answer 1

You can find the first day of this week:

 trunc('week', current_date) 

And on the first day of the first week of this month:

 trunc('week', date_trunc('month', current_date)) 

Subtract two to find the month of the month:

 extract('day' from date_trunc('week', current_date) - date_trunc('week', date_trunc('month', current_date))) / 7 + 1 

Answer 2

Try to execute

 SELECT to_char('2016-05-01'::timestamp, 'W'); 

Answer 3

I'm not sure how this works in postgreSql ( http://www.w3cyberlearnings.com/PostgreSQL_DATE_PART )
but in sql server the example looks something like this ...

 >SELECT date_part('week', date) 

Answer 4

You can find the week number using the day, as well as:

 select ((date_part('day', current_date)::integer - 1) / 7) +1; 

Answer 5

This will give accurate results.

CREATE OR REPLACE FUNCTION week_no(date) RETURNS integer AS $BODY$ SELECT CASE WHEN EXTRACT(DAY FROM $1::TIMESTAMP)::INTEGER = 1 THEN 1 ELSE extract(week from $1:: TIMESTAMP)::integer
- extract(week from ( date_trunc('month', $1::TIMESTAMP) + interval '1 day' ) )::integer + 1 END
$BODY$ LANGUAGE sql IMMUTABLE STRICT COST 100;

Example: week_no ('2017-01-01') = 1

Answer 6

Maybe late .. but it works too

where cast (call_start as Date) = Date 'today'

or cast (call_start as Date) = Date 'today' -1

or cast (call_start as Date) = Date 'today' -2

or cast (call_start as Date) = Date 'today' -3

or cast (call_start as Date) = Date 'today' -4

or cast (call_start as Date) = Date 'today' -5

or cast (call_start as Date) = Date 'today' -6

or cast (call_start as Date) = Date 'today' -7

;

it gives me this