0

I am trying to use the add_month function but getting an error. I want to get the number of visits between [CAL_DATE - 13 months] and [CAL_DATE]. The format of the dates are as following: 2007-14, 2010-05, 2009-04 and etc. this is the error I am getting "Bad time stamp external representation '2009-11" and here is the code I am using. I can't seem to figure out the issue.

CAL_DATE BETWEEN add_months(CAL_DATE,-13) AND CAL_DATE.

I am using netezza database.

Cœur
  • 37,241
  • 25
  • 195
  • 267
moe
  • 5,149
  • 38
  • 130
  • 197

1 Answers1

1

Presumably add_months expects a date as its first argument and returns a date. You don't have dates, you have YYYY-MM strings so you have two problems:

  1. add_months won't know what to do with a YYYY-MM string.
  2. BETWEEN won't know what to do with a date and a YYYY-MM string.

If you want to use add_months then you'll have to give it a date and convert the date it gives you to one of your YYYY-MM strings with something like this:

to_char(add_months((cal_date || '-01')::date, -13), 'yyyy-mm')

Appending -01 to your strings should give you a string representation of the first of that month and you should be able to cast that to a date with ::date. Then a to_char to convert the result of add_months back to your YYYY-MM format.

Alternatively, since add_months isn't really doing anything useful for you here, just use a PostgreSQL interval for the month adjustment:

to_char((cal_date || '-01')::date - interval '13 months', 'yyyy-mm')
mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • thanks. i initially had something like this: 2013-10-02 but i used the substring function to remove the day portion of the date: SUBSTRING (cal_date, 1, 7). so my question is, is there a way to keep the Year and the Month but replace the day with 01? – moe Oct 04 '13 at 19:59
  • Why do you want to replace a day that isn't there? Your `cal_date`s just contain the year and month so where would a 02 day come from? – mu is too short Oct 04 '13 at 20:03
  • the original format for the date field is yyyy-mm-dd but i had remove the day portion so i can group the data by year and month only. if i figure out how to replace 01 with with day portion then my problem will be solved. – moe Oct 04 '13 at 20:05
  • I'm confused. Your `cal_date`s are YYYY-MM-DD but they're also YYYY-MM? Please update your question to reflect reality. – mu is too short Oct 04 '13 at 20:08
  • date_trunc('month',to_Date('2013-10-02','YYYY-MM-DD')) will keep just the month and year – Niederee Nov 27 '13 at 06:12
  • @Niederee: Yeah, that's another option but `date_trunc` gives you a `timestamp` back. – mu is too short Nov 27 '13 at 06:19
  • You can wrap it with `date` like `date(date_trunc('month',to_Date('2013-10-02','YYYY-MM-DD')))` – Niederee Nov 27 '13 at 06:27