Toad World® Forums

DB2 - First And Last Day Of Previous Quarter

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.

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

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

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.[:’(]