How to get yesterday date starts at 00:00:00 in Vertica SQL?
Normally in SQL Server it will be like this SELECT DATEADD(Day, -1, DATEDIFF(Day, 0, GetDate())) 2021-01-31 00:00:00.000
How about in Vertica?
I would clearly go closer to the ANSI standard. There is an ANSI reserved word CURRENT_DATE, which is an expression that returns today's date (without the time). Many DBMSs support it (I actually think SQL Server, too); and I would stick to that - or to CURRENT_TIMESTAMP if I need the timestamp. "Never use DBMS specifics if you can avoid them".
In Vertica, you can subtract integers from or add integers to dates. You can't use SQL-Server's peculiar DATEADD(), but you can use the more standard (in other DBMSs) TIMESTAMPADD() which works like DATEADD() and returns a timestamp.
For completeness's sake: If you have a result timestamp with something else than '00:00:00' as the time element, use TRUNC(<timestamp>) to truncate the value and get a timestamp with the time at zero.
SELECT
CURRENT_DATE AS ANSI_CURRENT_DATE
, CURRENT_DATE-1 AS yesterday_midnight_minus
, TIMESTAMPADD(DAY,-1,CURRENT_DATE) AS tsadd
;
-- out ANSI_CURRENT_DATE | yesterday_midnight_minus | tsadd
-- out -------------------+--------------------------+---------------------
-- out 2021-02-01 | 2021-01-31 | 2021-01-31 00:00:00