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`
--------------------------------
A Decimal String Style of Datestamp from an Airtable to a Correct and True DateStamp in a Big Query.
---------------------------------------
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 also asked the Stackoverflow Community page and they have also their suggested answer.
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.
0 Comments