Find the Number of Days Passed and Remaining in the Relative Year

Jim Knicely authored this post. Although there aren’t any specific functions that will return the number of days that have passed and that are remaining in a given year, you can combine a few of Vertica’s built-in date functions to find these numbers. Example: dbadmin=> SELECT sysdate "Today", dbadmin-> DATEDIFF(day, TRUNC(sysdate, 'YY'), TRUNC(sysdate)) "# of days that have passed in current year", dbadmin-> DATEDIFF(day, TRUNC(sysdate), (ADD_MONTHS(TRUNC(sysdate, 'YY'), 12)-1)) "# of days remaining in current year"; Today | # of days that have passed in current year | # of days remaining in current year ----------------------------+--------------------------------------------+------------------------------------- 2018-08-06 11:56:03.111593 | 217 | 147 (1 row) You can encapsulate the date logic above into several user-defined functions that can be called later! dbadmin=> CREATE OR REPLACE FUNCTION days_passed_current_year (x TIMESTAMP) RETURN INT AS dbadmin-> BEGIN dbadmin-> RETURN DATEDIFF(day, TRUNC(x, 'YY'), TRUNC(x)); dbadmin-> END; CREATE FUNCTION dbadmin=> CREATE OR REPLACE FUNCTION days_remaining_current_year (x TIMESTAMP) RETURN INT AS dbadmin-> BEGIN dbadmin-> RETURN DATEDIFF(day, TRUNC(x), (ADD_MONTHS(TRUNC(x, ‘YY’), 12)-1)); dbadmin-> END; CREATE FUNCTION dbadmin=> SELECT x AS “Some Date”, dbadmin-> days_passed_current_year(x) AS “# of days that have passed in the relative year”, dbadmin-> days_remaining_current_year(x) AS “# of days remaining in the relative year” dbadmin-> FROM (SELECT ‘2018-JAN-01 08:00’::TIMESTAMP AS x dbadmin(> UNION ALL dbadmin(> SELECT ‘2018-DEC-31 08:00’::TIMESTAMP dbadmin(> UNION ALL dbadmin(> SELECT ‘2017-SEP-19 08:00’::TIMESTAMP) foo; Some Date | # of days that have passed in the relative year | # of days remaining in the relative year ———————+————————————————-+—————————————— 2018-01-01 08:00:00 | 0 | 364 2018-12-31 08:00:00 | 364 | 0 2017-09-19 08:00:00 | 261 | 103 (3 rows) Have fun!