Big Query (SQL): Date Problems (Solution)

    In my previous posts, I showed my problem regarding some challenges I have encountered concerning date range and date format.  So, I made some ways to solve it and meet the desired results. I'll show my suggested query on it. I hope it might help.

Getting a Monthly Date Range

    According to the posts, the table is named "Table_Range". The suggested query is illustrated below:

--------------------------------

SELECT 

,Delivery_Due_date

 

, if ( Delivery_Due_Date between date_add (date_sub(date_trunc ( Delivery_Due_Date, Month), interval 1 month),interval 29 day) and date_add(date_trunc( Delivery_Due_Date, Month), interval 27 day) ,date_add (date_sub(date_trunc( Delivery_Due_Date, Month), interval 1 month), interval 28 day), date_add(date_trunc(Delivery_Due_Date, Month), interval 28 day) ) as Starting_Date

 

, date_add (if ( Delivery_Due_Date between date_add (date_sub(date_trunc(Delivery_Due_Date, Month), interval 1 month), interval 29 day) and date_add(date_trunc( Delivery_Due_Date, Month), interval 27 day),date_add (date_sub(date_trunc(a. Delivery_Due_Date, Month), interval 1 month), interval 28 day), date_add(date_trunc(Delivery_Due_Date, Month), interval 28 day) ), interval 1 month )-1 as Ending_Date

 

from `Table_Range`

 

--------------------------------

    

    Based on the query, I used date functions (i.e. date_add, date_sub, and date_trunc) in getting a range on it.

  

I also asked the Stackoverflow Community page and they have also their suggested answer.

A Decimal String Style of Datestamp from an Airtable to a Correct and True DateStamp in a Big Query.

    According to the posts, the table is named "Airtable". The suggested query is illustrated below:

---------------------------------------

With Decimal as 

(cast ("1899-12-30" as date) + cast ( split (Delivery_due_date, ".") [offset(0)] as Int64) as Delivery_date

,  cast (format ('%4f', cast (concat (0, "." , split ( Delivery_due_date, '.') [offset(1)]) as numeric)*24) as String) as Delivery_time

from `Airtable`)

, Decimal1 as

(select

Delivery_date

, Delivery_time  

, split (Delivery_time, '.') [offset (0)] as Delivery_hour

, split  (cast (format ('%4f', cast (concat (0, '.', split (Delivery_time , '.') [offset (1)]) as numeric)*60) as String), '.' ) [offset (0)] as Delivery_minute

from Decimal)

(select 

,cast (concat (Delivery_date ,' ', Delivery_hour, ':', Delivery_minute , ':', '00') as Datetime) as True_date 

from Decimal1

)

---------------------------------------

    I used some format functions (i.e. cast, concat, split-offset, and format) in getting the desired result.

    I also asked the Stackoverflow Community page and they have also their suggested answer.

    

    In conclusion, BigQuery is so very flexible yet it is very complex. But using your creativity in applying their numerous functions, you can get your desired results.   

  f you want to share your suggested ways to solve it, I am very lovely and thankful to see and read on it. Just comment on this post for your suggested queries.