3
Select to_date(to_char(date_part('year',current_date),'0000') || trim(to_char(date_part('month',current_date),'00')) || '01','YYYYMMDD')

So far, this is the best I can come up with.

I am also unable to find a comprehensive language reference for Netezza SQL that has all functions in it, so please include a source in your answer.

Nathan T Alexander
  • 257
  • 3
  • 15

2 Answers2

6

Use date_trunc('month', current_ date), which is documented here.

ScottMcG
  • 3,867
  • 2
  • 12
  • 21
1

Start with the date, for what I needed today, was using current date/month. Then find the last day of the current month. That's the outer shell for the first day as a systematic output:

  1. start with the current date
  2. use add_months (-1) to subtract a month;
  3. find the last day of the previous month;
  4. add 1 day.

I then decided to test out on February (leap year and normal), finding the first from the first, and then adding in finding the first of the next month. Also checked looking at a month ago from a month ending 31st and 30th. I think this should be flexible.

select current_date, 
    last_day(add_months(current_date,-1))+1 as firstdt_currmos,
    last_day(current_date) as lastdt_currmos
;

select last_day(add_months('02-29-2016',-1))+1 as firstdt_febleap,
    last_day(add_months('02-28-2019',-1))+1 as firstdt_febnorm,
    last_day(add_months('07-01-2019',-1))+1 as firstdt_first,
    last_day(add_months(current_date,-1))+1 as firstdt_currmos,
    last_day(current_date)+1 as firstdt_nextmos,
    last_day(add_months('07-31-2019',-1))+1 as firstdt_mos31st,

    date(add_months('07-31-2019',-1)) as mosago_31st,
    date(add_months('06-30-2019',-1)) as mosago_30th

;
jschicago
  • 11
  • 3