# Date format

**URL:** <https://forums.toadworld.com/t/date-format/34320>\
**Category:** Toad for Oracle & DevOps Toolkit\
**Created:** [October 19, 2015, 12:56am UTC](https://forums.toadworld.com/t/date-format/34320 "2015-10-19T00:56:10Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![AxelGG](https://avatars.discourse-cdn.com/v4/letter/a/e5b9ba/32.png) [@AxelGG](https://forums.toadworld.com/u/AxelGG)\
**Post date:** [October 19, 2015, 12:56am UTC](https://forums.toadworld.com/t/date-format/34320/1 "2015-10-19T00:56:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\
**Post date:** [October 19, 2015, 2:29am UTC](https://forums.toadworld.com/t/date-format/34320/2 "2015-10-19T02:29:27Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![AxelGG](https://avatars.discourse-cdn.com/v4/letter/a/e5b9ba/32.png) [@AxelGG](https://forums.toadworld.com/u/AxelGG)\
**Post date:** [October 19, 2015, 4:23am UTC](https://forums.toadworld.com/t/date-format/34320/3 "2015-10-19T04:23:14Z")

</div>

Thank you very much, Norm [TeamT] - it works! 🙂

---

<div class="post-metadata">

**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\
**Post date:** [October 19, 2015, 4:56am UTC](https://forums.toadworld.com/t/date-format/34320/4 "2015-10-19T04:56:50Z")

</div>

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