sqlsnowflake-cloud-data-platformdayofmonth

DAY Function Reverse

I am currently trying to get the reverse day of a month. So if its day 1 of Month March, the reverse day is 31 and so on.

There are mulitple ways to get the day of the month, but I can't think of a way to get the reverse (especially when you have months with less then 31 days).

I am working on Snowflake and to get the Day of the Month I'll use the day() function.

select 
  '2016-01-02T23:39:20.123-07:00'::timestamp as tstamp,
   day(tstamp) as "DAY OF MONTH";

This results in

TSTAMP                       DAY OF MONTH
2016-01-02 23:39:20.123            2

Now could anyone help me to get the reverse order? In this example it should be the 30 day of Month.

Solution

  • You could use:

    select day(last_day(current_date)) - day(current_date) + 1