# date question

**URL:** <https://forums.toadworld.com/t/date-question/40948>\
**Category:** Toad for IBM DB2\
**Created:** [May 21, 2018, 2:31pm UTC](https://forums.toadworld.com/t/date-question/40948 "2018-05-21T14:31:23Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![dkirby](https://avatars.discourse-cdn.com/v4/letter/d/ecc23a/32.png) [@dkirby](https://forums.toadworld.com/u/dkirby)\
**Post date:** [May 21, 2018, 2:31pm UTC](https://forums.toadworld.com/t/date-question/40948/1 "2018-05-21T14:31:23Z")

</div>

I am trying to do a date calc that has me confused. i want to use a date in the past to as my starting point to get the most recent full 12 mont period

3/1/2003 = 3/1/2017 - 3/1/2018

6/1/2005 = 6/1/2016 - 6/1/2017

does this make sense? any help appreciated.

---

<div class="post-metadata">

**Author:** ![Ondrej.Zizka](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/ondrej.zizka/32/314_2.png) [@Ondrej.Zizka](https://forums.toadworld.com/u/Ondrej.Zizka)\
**Post date:** [May 22, 2018, 2:12am UTC](https://forums.toadworld.com/t/date-question/40948/2 "2018-05-22T02:12:39Z")

</div>

Hello,

Does exist any rule on how to compute the date? I don’t see any for these 2 examples. If there is no rule, you would need to implement it in the code manually or save combinations to a table and load related dates from the table when necessary.

Regards  
Ondrej

---

<div class="post-metadata">

**Author:** ![Db2Developer](https://avatars.discourse-cdn.com/v4/letter/d/b38774/32.png) [@Db2Developer](https://forums.toadworld.com/u/Db2Developer)\
**Post date:** [May 22, 2018, 6:23am UTC](https://forums.toadworld.com/t/date-question/40948/3 "2018-05-22T06:23:20Z")

</div>

I am confused. Can you explain in greater detail? I dont understand how you are getting 3/1/2003 when you subtract 3/1/2017 and 3/1/2018. What is the formula you are using? Also are you using z/OS or LUW?

---

<div class="post-metadata">

**Author:** ![dkirby](https://avatars.discourse-cdn.com/v4/letter/d/ecc23a/32.png) [@dkirby](https://forums.toadworld.com/u/dkirby)\
**Post date:** [May 22, 2018, 6:37am UTC](https://forums.toadworld.com/t/date-question/40948/4 "2018-05-22T06:37:29Z")

</div>

I am trying to get the most current 12 months of data based on a start date. so in this instance the start date is 3/1/2003. The most current 12 months would be from 3/1/2017 – 3/1/2018. The next record has a start date of 6/4/2006 … the most current full 12 months based on that date would 6/4/2016 -6/4/2017… does that help?

**From:** Db2Developer [[mailto:bounce-Db2Developer@toadworld.com](mailto:bounce-Db2Developer@toadworld.com)]

**Sent:** May 22, 2018 8:30

**To:** [toaddb2@toadworld.com](mailto:toaddb2@toadworld.com)

**Subject:** RE: [Toad for IBM DB2 - Discussion Forum] date question

**[RE: date question](https://community.toadworld.com/products/toad-for-ibm-db2/f/18/p/35367/70608#70608)**

Reply by Db2Developer

I am confused. Can you explain in greater detail? I dont understand how you are getting 3/1/2003 when you subtract 3/1/2017 and 3/1/2018. What is the formula you are using? Also are you using z/OS or LUW?

To reply, please reply-all to this email.

[Stop receiving emails](https://community.toadworld.com/products/toad-for-ibm-db2/f/18/t/35367?subscribe=false) on this subject.

Or [  
Unsubscribe from Toad for IBM DB2 Forum](https://community.toadworld.com/products/toad-for-ibm-db2/f/18?subscribe=false) notifications altogether.

Toad for IBM DB2 - Discussion Forum

[Flag](https://community.toadworld.com/products/toad-for-ibm-db2/f/18/p/35367/70608?AbuseContentId=97fde689-946a-4d6a-a8ee-42532021e0fb&AbuseContentTypeId=f586769b-0822-468a-b7f3-a94d480ed9b0&AbuseFlag=true)  
this post as spam/abuse.

* * *

DJ Kirby

Business Analyst

Bay and Bay Transportation

[dkirby@bayandbay.com](mailto:dkirby@bayandbay.com)

Tel: 651-480-4912

Fax: 651-480-7996

Cell:612-325-4362

[www.bayandbay.com](http://www.bayandbay.com)

* * *

This message is private and confidential. If you have received this message in error, please notify us and remove it from your system.

---

<div class="post-metadata">

**Author:** ![Db2Developer](https://avatars.discourse-cdn.com/v4/letter/d/b38774/32.png) [@Db2Developer](https://forums.toadworld.com/u/Db2Developer)\
**Post date:** [May 22, 2018, 6:43am UTC](https://forums.toadworld.com/t/date-question/40948/5 "2018-05-22T06:43:00Z")

</div>

Ah now I follow. I will try to write something up for you. Seems like pretty simple SQL. Most likely just put the SQL into a function and call that function. Let me get back to you shortly.

Are you using Db2 for z/OS or LUW? There is different functionality between them.

---

<div class="post-metadata">

**Author:** ![kevin\_krause](https://avatars.discourse-cdn.com/v4/letter/k/f0a364/32.png) [@kevin\_krause](https://forums.toadworld.com/u/kevin_krause)\
**Post date:** [May 22, 2018, 6:46am UTC](https://forums.toadworld.com/t/date-question/40948/6 "2018-05-22T06:46:00Z")

</div>

This makes absolutely no sense. What is the correlation between the start date and the most current 12 months of data. Until you provide that I don’t know how the question can be answered.

---

<div class="post-metadata">

**Author:** ![Db2Developer](https://avatars.discourse-cdn.com/v4/letter/d/b38774/32.png) [@Db2Developer](https://forums.toadworld.com/u/Db2Developer)\
**Post date:** [May 22, 2018, 6:53am UTC](https://forums.toadworld.com/t/date-question/40948/7 "2018-05-22T06:53:37Z")

</div>

How does this work? Not sure about performance, but I think its what you want.

with cte\_dates as  
(  
Select cast(‘5/22/2018’ as date) as start\_date  
from sysibm.sysdummy1

union all

Select cast(‘3/1/2003’ as date) as start\_date  
from sysibm.sysdummy1

union all

Select cast(‘6/4/2006’ as date) as start\_date  
from sysibm.sysdummy1  
)  
Select a.start\_date,  
case  
–last year  
when month(a.start\_datE) \<= month(current timestamp)  
then month(a.start\_date)  
|| ‘/1/’  
|| cast(cast(year(current timestamp)as int) -1 as char(4))  
–2 years ago  
else  
month(a.start\_date)  
|| ‘/1/’  
|| cast(cast(year(current timestamp)as int) -2 as char(4))  
end as begin\_date,  
case  
–current year  
when month(a.start\_datE) \<= month(current timestamp)  
then month(a.start\_date)  
|| ‘/1/’  
|| year(current timestamp)  
–last year  
else  
month(a.start\_date)  
|| ‘/1/’  
|| cast(cast(year(current timestamp)as int) -1 as char(4))  
end as end\_date  
from cte\_dates a

![7840.toad.png](https://global.discourse-cdn.com/quest/original/2X/1/194a44cef0015fe1b00bf556cd8dd6fe467acf23.png)