# DB2 - First And Last Day Of Previous Quarter

**URL:** <https://forums.toadworld.com/t/db2-first-and-last-day-of-previous-quarter/40295>\
**Category:** Toad for IBM DB2\
**Created:** [November 29, 2017, 4:26am UTC](https://forums.toadworld.com/t/db2-first-and-last-day-of-previous-quarter/40295 "2017-11-29T04:26:47Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![riccarddo](https://avatars.discourse-cdn.com/v4/letter/r/b5a626/32.png) [@riccarddo](https://forums.toadworld.com/u/riccarddo)\
**Post date:** [November 29, 2017, 4:26am UTC](https://forums.toadworld.com/t/db2-first-and-last-day-of-previous-quarter/40295/1 "2017-11-29T04:26:47Z")

</div>

Dear All,

I’m a SQL Server guy but we do have a system that uses DB2. As per the subject above, I can easily do this in Transact-SQL.But in DB2 SQL, I am having some difficulty.

So, looking ar the SQL below, this works fine:

**SELECT d - DAYOFYEAR(d) DAYS + ((QUARTER(d) - 1) \* 3) MONTHS - 3 MONTHS + 1 DAY**  
\*\* FROM (VALUES DATE(‘2017-04-21’)) as D (d)\*\*

But once, I wrap in common table expression (that’s what they are called in Transact-SQL) and I do a SELECT, I get no joy.

**WITH StarDate**

**(**

**SELECT d - DAYOFYEAR(d) DAYS + ((QUARTER(d) - 1) \* 3) MONTHS - 3 MONTHS + 1 DAY**  
**FROM (VALUES DATE(‘2017-04-21’)) as D(d)**

**)**

\_\_SELECT \* FROM StarDate \_\_ – This doesn’t work!

Any ideas, please?

Regards.

---

<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:** [November 29, 2017, 5:56am UTC](https://forums.toadworld.com/t/db2-first-and-last-day-of-previous-quarter/40295/2 "2017-11-29T05:56:54Z")

</div>

Hello riccarddo,

Please use the following:

WITH StarDate **(firstday) as**  
(  
SELECT d - DAYOFYEAR(d) DAYS + ((QUARTER(d) - 1) \* 3) MONTHS - 3 MONTHS + 1 DAY  
FROM (VALUES DATE(‘2017-04-21’)) as D(d)  
)

SELECT \* FROM StarDate

This works in DB2 LUW.

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:** [November 29, 2017, 6:30am UTC](https://forums.toadworld.com/t/db2-first-and-last-day-of-previous-quarter/40295/3 "2017-11-29T06:30:55Z")

</div>

Does this work for you? It’s kinda of sloppy and only returns 1 date, but you can tweak it I think. The key I think is you need to use sysibm.sysdummy1 in z/OS. It’s sad that z/OS lacks behind alot of features of T-SQL in SQL Server…

with cteDate as (  
Select date(‘04/21/2017’) as d  
from SYSIBM.sysdummy1  
)  
,StarDate as  
(  
SELECT d - DAYOFYEAR(d) DAYS + ((QUARTER(d) - 1) \* 3) MONTHS - 3 MONTHS + 1 DAY as DateValue  
FROM cteDate  
)  
Select \* from StarDate

–This will return a value of 01/01/2017

---

<div class="post-metadata">

**Author:** ![riccarddo](https://avatars.discourse-cdn.com/v4/letter/r/b5a626/32.png) [@riccarddo](https://forums.toadworld.com/u/riccarddo)\
**Post date:** [November 29, 2017, 8:26am UTC](https://forums.toadworld.com/t/db2-first-and-last-day-of-previous-quarter/40295/4 "2017-11-29T08:26:26Z")

</div>

Thanks very much Ondrej! Much appreciated. [Y]

After sending the post, I realised that I was missing the **AS** keyword in my CTE.

After working with the ease and intuitiveness of SQL Server/T-SQL for about 20 years, it’s sometimes frustrating to work with the likes of DB2.[:’(]