Labels

Apache Hadoop (3) ASP.NET (2) AWS S3 (2) Batch Script (3) BigQuery (21) BlobStorage (1) C# (3) Cloudera (1) Command (2) Data Model (3) Data Science (1) Django (1) Docker (1) ETL (7) Google Cloud (5) GPG (2) Hadoop (2) Hive (3) Luigi (1) MDX (21) Mongo (3) MYSQL (3) Pandas (1) Pentaho Data Integration (5) PentahoAdmin (13) Polybase (1) Postgres (1) PPS 2007 (2) Python (13) R Program (1) Redshift (3) SQL 2016 (2) SQL Error Fix (18) SQL Performance (1) SQL2012 (7) SQOOP (1) SSAS (20) SSH (1) SSIS (42) SSRS (17) T-SQL (75) Talend (3) Vagrant (1) Virtual Machine (2) WinSCP (1)

Sunday, September 10, 2017

BigQuery Get First and Last Day of Months

The below Query helps you to get the First Day and Last Day of a month. This can also be used in TABLE_DATE_RANGE to retrieve data for Previous Months.

SQL

SELECT DATE_ADD(DATE_ADD(CURRENT_DATE(),-DAY(CURRENT_DATE())+1,"DAY") ,-1,"MONTH") First_Day_Previous_Month,
       DATE_ADD(DATE_ADD(CURRENT_DATE(),-DAY(CURRENT_DATE()),"DAY") ,0,"MONTH") Last_Day_Previous_Month,

       DATE_ADD(DATE_ADD(CURRENT_DATE(),-DAY(CURRENT_DATE())+1,"DAY") ,0,"MONTH") First_Day_Current_Month