CODEWITHSUNDEEP
google-bigquery
Pradeep
Pradeep

Reputation: 6603

Get total no of days in given month in Google BigQuery?

I can create user defined function and compute total no of days for given month. Is there a direct way of getting total of no days in given month for given date

eg.

select date_t, some_inbuilt_function_get_total_no_of_days_in_month(date_t)

There is month function to get month from given date, similarly there is date function, so I can go to start of the month. There is date difference function but there is no way to go to end of the month.

Reference: https://cloud.google.com/bigquery/docs/reference/legacy-sql

Upvotes: 8

Views: 14295

Answers (7)

Peter Jonker
Peter Jonker

Reputation: 1

Try running this query. It first get all the dates between date input, then calculates the number of days in the month and after that the remaining days in the mont:

  WITH
      dates AS(
      SELECT
        date
      FROM
        UNNEST(GENERATE_DATE_ARRAY(DATE('2022-05-01'), DATE('2022-06-30')) ) AS date
      ORDER BY
        date ASC),
      months AS(
      SELECT
        date,
        EXTRACT(month
        FROM
          date) AS month
      FROM
        dates
      GROUP BY
        1,
        2)
    SELECT
      date,
      month,
      COUNT(date) OVER (PARTITION BY month) AS month_day_count,
      (COUNT(date) OVER (PARTITION BY month) - ROW_NUMBER() OVER(PARTITION by month)) +1 as month_days_left
    FROM
      months

Upvotes: 0

Islam Azab
Islam Azab

Reputation: 747

A simpler way would be just

EXTRACT(DAY FROM LAST_DAY(<DATE>))

As a function

CREATE TEMP FUNCTION DaysInMonth(d DATE) AS (
  EXTRACT(DAY FROM LAST_DAY(d))
);

SELECT DaysInMonth('2017-01-17');

Upvotes: 16

Serhii Puzyrov
Serhii Puzyrov

Reputation: 21

This is my solution, just replace "2020-06-01" with your value:

EXTRACT(DAY FROM (DATE_SUB(DATE_TRUNC(DATE_ADD("2020-06-01", INTERVAL 1 MONTH), MONTH),INTERVAL 1 DAY)))

Upvotes: 2

Rochelle Smits
Rochelle Smits

Reputation: 41

I wanted to calculate the number of days in the current month and came up with the following solution based on Pradeep's answer:

select distinct
 extract(DAY FROM 
    date_sub(date(CASE WHEN extract(MONTH from current_timestamp()) < 12 THEN extract(YEAR from current_timestamp()) -- but if this is 12, make it 1
          ELSE extract(YEAR from current_timestamp()) + 1
          END
       ,CASE WHEN extract(MONTH from current_timestamp()) < 12 THEN extract(MONTH from current_timestamp()) + 1 
          ELSE 1 --if it's decemer, the next month is january. 
          END  
        ,1), INTERVAL 1 DAY)) DAYS_IN_MONTH

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

This can be matter of aesthetic preferences, but below looks more elegant to me 

#standardSQL
CREATE TEMP FUNCTION DaysInMonth(d DATE) AS (
  32 - EXTRACT(DAY FROM DATE_ADD(DATE_TRUNC(d, MONTH), INTERVAL 31 DAY))
);

SELECT DaysInMonth('2017-01-17');

And somehow I feel will be more optimal on big volume (if there is any use case for this)

Below is yet another version - less optimal 

#standardSQL
CREATE TEMP FUNCTION DaysInMonth(d DATE) AS (
  EXTRACT(DAY FROM DATE_SUB(DATE_ADD(d, INTERVAL 1 MONTH), 
                            INTERVAL EXTRACT(DAY FROM d) DAY))
);

SELECT DaysInMonth('2017-01-17')

And if for any reason you need it in BigQuery Legacy SQL, check below example (but still consider migrating to Standard SQL) 

#legacySQL
SELECT 
  32 - DAY(DATE_ADD(UTC_USEC_TO_MONTH(PARSE_UTC_USEC(d)),31,'DAY')) as DaysInMonth
FROM (SELECT '2017-01-17' as d)

Upvotes: 10

Elliott Brossard
Elliott Brossard

Reputation: 33745

It's easier using standard SQL. You can define your own function to simplify your query, too.

#standardSQL
CREATE TEMP FUNCTION DaysInMonth(d DATE) AS (
  DATE_DIFF(DATE_TRUNC(DATE_ADD(d, INTERVAL 1 MONTH), MONTH),
            DATE_TRUNC(d, MONTH), DAY)
);

SELECT DaysInMonth('2017-01-17');

Upvotes: 4

Pradeep
Pradeep

Reputation: 6603

I found one way to go to end of the month,

We can use DATE ADD function to go to next month and then substract 1 day to go to end of the month.

SELECT
  DATE_ADD(TIMESTAMP(CONCAT(STRING(YEAR(DATE_ADD(TIMESTAMP("2017-1-12"),1, "Month"))),"-",STRING(MONTH(DATE_ADD(TIMESTAMP("2017-1-12"),1, "Month"))),"-1")), -1, "DAY")

Here is the answer: Just replace 2017-1-12 with desired date

SELECT
  DATEDIFF(TIMESTAMP(CONCAT(STRING(YEAR(DATE_ADD(TIMESTAMP("2017-1-12"),1, "Month"))),"-",STRING(MONTH(DATE_ADD(TIMESTAMP("2017-1-12"),1, "Month"))),"-1")), 
  TIMESTAMP(CONCAT(STRING(YEAR(TIMESTAMP("2017-1-12"))),"-",STRING(MONTH(TIMESTAMP("2017-1-12"))), "-1")))

Upvotes: 0

Related Questions