How do I get the first day of the previous year through yesterday one year ago?

Question

For example, I'm current hard-coding it like this

  "ACCOUNT1"."account_opendate" >= '01/01/2021' and
  "ACCOUNT1"."account_opendate" <= '05/08/2021'

I would like to instead use a dateadd like in SQL, but I keep getting an error.

I tried something like this and got an error:

  "ACCOUNT1"."account_opendate" >=  DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0)

I'd also like to have another query where I get the first day of the current month one year ago, so...

  "ACCOUNT1"."account_opendate" >= '05/01/2021'

Any ideas?

Answer 1

There are two questions here. Let's tackle the first one. The reason you are likely having issues with the dates is that Drill doesn't automatically recognize text as dates. Drill is schema free (sort of) and so trying to compare strings that way, won't work.

In any event, the way to convert a string field to a date is with the CAST() function. Thus, you could do something like:

WHERE CAST(`ACCOUNT1`.`account_opendate` AS DATE) >= CAST('01/01/2021' AS DATE) AND
CAST(`ACCOUNT1`.`account_opendate` AS DATE) <= CAST('05/08/2021' AS DATE)

Take a look at the Drill date time function documentation for more info about Drill's date/time functions. Drill does have DATEADD and many others.

One more thing. Drill uses backticks rather than double quotes for identifiers.

For the second part, I'm not sure exactly how to do that, but I'd think you could construct a date by extracting the current month, then subtracting one year. You might also be able to do it with the NEARESTDATE function.