0

I need to automate the date range of my monthly queries using Legacy SQL language.

With this code, Google Bigquery doesn't storage data tables from start to the end of the month (1st May to 31th May).

FROM TABLE_DATE_RANGE([99999999.ga_sessions_],
             DATE_ADD(CURRENT_TIMESTAMP(),-30,'DAY'),
             DATE_ADD(CURRENT_TIMESTAMP(),-1,'DAY'))
Mario M.
  • 802
  • 11
  • 26

1 Answers1

-1

made this in MSsql (its not pretty but i think you will catch the drift)

select cast('01.'+cast(Month(CURRENT_TIMESTAMP) as nvarchar)+'.'+cast(Year(CURRENT_TIMESTAMP) as nvarchar) as Date) as current_month_InitDate from mytable
select Dateadd(day, -1,cast('01.'+cast((Month(CURRENT_TIMESTAMP)+1) as nvarchar)+'.'+cast(Year(CURRENT_TIMESTAMP) as nvarchar) as Date)) as current_month_EndDate from mytable
N. P.
  • 1
  • 1
    this doesn't apply on BigQuery at all, its about the FROM statement not a field in the DB. This concept doesn't exists in MSsql – Pentium10 Jun 21 '17 at 08:39
  • I wrote an example how to get the first day and last day of the actual month DATE_ADD(CURRENT_TIMESTAMP(),-30,'DAY') would be the first of the month in your example -> cast('01.'+cast(Month(CURRENT_TIMESTAMP) as nvarchar)+'.'+cast(Year(CURRENT_TIMESTAMP) as nvarchar) as Date) so in this legacy SQL there are no functions to extract the month and year from a timestamp cast them to varchar and add 01 in front just to cast the whole construct to a Date again ? you could then get the last of the current via getting the first of the following month and to substract 1 Day – N. P. Jun 21 '17 at 09:09