No matching signature for operator BETWEEN for argument types: INT64, STRING, STRING-CodePudding

I am using the code below to try and change the column into the quarters the dates fall into. However, when I try to use the code I get an error message that reads "No matching signature for operator BETWEEN for argument types: INT64, STRING, STRING. Supported signature: (ANY) BETWEEN (ANY) AND (ANY) at [8:17]"

SELECT 
  INDEX,

  CASE 
    WHEN G_DATE BETWEEN '20110101' AND '20110331' THEN 20111
    WHEN G_DATE BETWEEN '20110401' AND '20110631' THEN 20112
    WHEN G_DATE BETWEEN '20110701' AND '20110930' THEN 20113
    WHEN G_DATE BETWEEN '20111001' AND '20111231' THEN 20114

    WHEN G_DATE BETWEEN '20120101' AND '20120331' THEN 20121
    WHEN G_DATE BETWEEN '20120401' AND '20120631' THEN 20122
    WHEN G_DATE BETWEEN '20120701' AND '20120930' THEN 20123
    WHEN G_DATE BETWEEN '20121001' AND '20121231' THEN 20124
    
    WHEN G_DATE BETWEEN '20130101' AND '20130331' THEN 20131
    WHEN G_DATE BETWEEN '20130401' AND '20130631' THEN 20132
    WHEN G_DATE BETWEEN '20130701' AND '20130930' THEN 20133
    WHEN G_DATE BETWEEN '20131001' AND '20131231' THEN 20134

    WHEN G_DATE BETWEEN '20140101' AND '20140331' THEN 20141
    WHEN G_DATE BETWEEN '20140401' AND '20140631' THEN 20142
    WHEN G_DATE BETWEEN '20140701' AND '20140930' THEN 20143
    WHEN G_DATE BETWEEN '20141001' AND '20141231' THEN 20144

    WHEN G_DATE BETWEEN '20150101' AND '20150331' THEN 20151
    WHEN G_DATE BETWEEN '20150401' AND '20150631' THEN 20152
    WHEN G_DATE BETWEEN '20150701' AND '20150930' THEN 20153
    WHEN G_DATE BETWEEN '20151001' AND '20151231' THEN 20154

FROM Data_table

CodePudding user response：

Your dates are likely string format, causing problems when trying to use "between". It's best to cast them as date and then extract the year and quarter from the dates. Here we concatenate them...

select  
  extract(year from CAST(g_date AS DATE FORMAT 'YYYYMMDD')) || 
  extract(quarter from CAST(g_date AS DATE FORMAT 'YYYYMMDD')) as yyyyq
from your_table

Output:

yyyyq
20141
20142
...

CodePudding user response：

You can always convert all the columns to DATE as suggested in other answers. However, STRINGS do support the BETWEEN clause. Looking at the error, the most likely problem is that the column G_DATE is an INTEGER and you are comparing it to strings. Converting this column to STRING will also work.