At the low level, see man page for date + option.
The problem with date and getdate() is that you do not want to be tied to the clock at any low level, ever, except writing history rows and similar real time activities. You should be able to test with arbitrary data/dates, or if there is an outage, clean up on a later day.
You probably want each archive file to hold all of one day. You could select each distinct date old enough to archive and loop for each date at a high level to archive and delete, so it automatically does more days if it fails to run some days.
T-SQL can can convert date strings using select without from eith in a scalar subquery to load a variable.
You do not want to convert ever row in the table if you can avoid it, you want to convert your bound(s) constants, especially if there is a useful index. BTW, a table clustered on time is lock-prone for non-batch use, as all users are churning at the end of the table.
You want to ensure that either:
- the date_time_col is a string, or
- the date_time_col is always loaded with a date and no time, or
- the date_time_col is processed in a range for the one day, e.g., "date_time_col >= day and date_time_col < (day + 1)".
Wasting a date-time datatype with just date is confusing, too, so often designers use either a CCYYMMDD string, a CCYYMMDD as literal int or a int days since epoch date. We all have 8 byte wide CPUs these days, so getting it down to 4 bytes is mostly useful for space saving.