Does anyone know how to query this SQL in Bigquery SQL?-CodePudding

SELECT 
    REPORTDATE,ARCHIVEDATE, RATE,GRID48_DR,
    REGEXP_REPLACE(SUBSTR(GRID48_DR, 1, MNTHS_BTWN - 1),
                   '\[\*a-zA-Z0-9\]', 'X') ||RATE||  
       SUBSTR(GRID48_DR, 1, LENGTH(GRID48_DR) - (MNTHS_BTWN)) GRID48_NEW
FROM 
    (SELECT  
         REPORTDATE, ARCHIVEDATE, RATE, GRID48_DR,
         MONTHS_BETWEEN(to_date(ARCHIVEDATE, 'MMYYYY'), to_date(REPORTDATE, 'MMYYYY'))   1 AS MNTHS_BTWN
     FROM TRADE)

In the Months Between function I am trying to find the no. of months ( 1) between a Timestamp data type date(archivedate) and String type date reportdate. How to do in BQ?

How do I replace the "SELECT REPORTDATE,ARCHIVEDATE,RATE,GRID48_DR,MONTHS_BETWEEN(to_date(ARCHIVEDATE,'MMYYYY'),to_date(REPORTDATE,'MMYYYY')) 1 AS MNTHS_BTWN" in BG ?

CodePudding user response：

You can use the EXTRACT( ) method to get any datepart from the Timestamp column. Here is an example:

EXTRACT(month FROM dateColumnName)

Refer to the documentation for more details : https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#extract

CodePudding user response：

Try below query using date_diff function of Bigquery.

(SELECT  
         REPORTDATE, ARCHIVEDATE, RATE, GRID48_DR,
         date_diff(to_date(ARCHIVEDATE, 'MMYYYY'), to_date(REPORTDATE, 'MMYYYY'),month)   1 AS MNTHS_BTWN
     FROM TRADE)