Hi,
I am a newby in SQL, hence I have a simple question:
"How shall I modify
BETWEEN to_date(ADD_MONTHS(-4,‘2015/08/31’), ‘yyyy/mm/dd hh24:mi:ss’) AND to_date(‘2015/08/31 00:00:00’, ‘yyyy/mm/dd hh24:mi:ss’)
in order to make a rolling window work with only one date entry - the end period date, instead of manually updating the start date as well?"
I am using Toad Data Point for Oracle.
Many thanks in advance for your time!
Best wishes,
Aleks
Hi Aleks,
I’m sorry but I’m not 100% certain of what you require here, but I’ll have a go …
First off, it looks a little wrong, the BETWEEN clause you posted.
ADD_MONTHS works on DATE (in this case) data types. Yours looks to be working on a string - so this might be a better solution, also, the string literal has no time portion, so the time portion of the format string is not necessary - it defaults to 0 hours, 0 minutes and 0 seconds:
BETWEEN ADD_MONTHS(to_date(‘2015/08/31’, ‘yyyy/mm/dd’), -4) AND to_date(‘2015/08/31’, ‘yyyy/mm/dd’)
However, I get the impression that you do not want to be editing the code all the time, so you should use bind variables, or substitution variables, in Toad:
BETWEEN ADD_MONTHS(to_date(’&end_date_as_yyyymmdd’, ‘yyyymmdd’), -4) AND to_date(’&&end_date_as_yyyymmdd’, ‘yyyymmdd’)
You only have to type in the date as yyyymmdd, no quotes or ‘/’ separators etc are required. In Toad you will be prompted to supply two variables, but you need only supply the first with one ampersand, then click OK. The same value will be used for both. The ampersands have the following meaning:
&xxx = prompt me for a value for this variable and call it xxx.
&&xxx = do not prompt me, unless xxx has no current value, just use the existing value.
HTH
Thank you very much, Norm [TeamT] - it works! 
Hi Aleks,
just one thing. Oracle DATEs begin at midnight (dd/mm/yyyy 00:00:00) and end at 23:59:59 later that same day (dd/mm/yyyy 23:59:59) so, just be aware that the above will only include DATEs that you enter which fall on or after midnight on your entered date (dd/mm/yyyy 00:00:00) minus 4 months but on or BEFORE midnight on the date entered. We “lose” 23 hours, 59 minutes and 59 seconds of your entered date.
If this is unsuitable, best be explicit:
**BETWEEN ADD_MONTHS(to_date(‘end_date_as_yyyymmdd’ || ‘_00:00:00’, ‘yyyymmdd_hh24:mi:ss’), -4) AND to_date(‘end_date_as_yyyymmdd’ || ‘_23:59:59’, ‘yyyymmdd_hh24:mi:ss’)
**You need to tag on the start and end of day hours, minutes and seconds to be sure that a date on the ending date that you supply, say 07:00:00 in the morning, will be included - if that is what is desired.
HTH