DB2 Newbie - Query Help

maxwellhouse · #1 (**permalink**) 10-13-10, 13:46

I am a SQL Server guy, was hoping someone could help me accomplish the following using DB2.

The below query works fine in SQL Server. I am having difficulties doing something similar in DB2.
Specially, I am trying to pull all records out of a DB2 table
WHERE
TRAN_TS >= first day of previous month
AND
TRAN_TS < first day of current month

WORKING SQL CODE:

select * from atable
where
TRAN_TS >= DateAdd(month, DateDiff(month, 0, getdate())-1, 0)
and
TRAN_TS < DateAdd(month, DateDiff(month, 0, getdate()), 0)

Does anyone know how to do the same in DB2?

Thanks

mdx34 · #2 (**permalink**) 10-13-10, 15:21

You need to calculate the dates in DB2. How about,

first_day_last_month="select date('0001-01-01') + year(current date) years - 1 year + month(current date) months - 2 months from sysibm.sysdummy1"

first_day_this_month="select date('0001-01-01') + year(current date) years - 1 year + month(current date) months - 1 month from sysibm.sysdummy1"

tonkuma · #3 (**permalink**) 10-13-10, 15:36

There are some ways(I like first examples):

first day of previous month ===> LAST_DAY(CURRENT DATE) + 1 DAY - 2 MONTHs
first day of previous month ===> CURRENT DATE - ( DAY(CURRENT DATE) - 1 ) DAYs - 1 MONTH

first day of current month ===> LAST_DAY(CURRENT DATE) + 1 DAY - 1 MONTH
first day of current month ===> CURRENT DATE - ( DAY(CURRENT DATE) - 1 ) DAYs
first day of current month ===> DATE( TRANSLATE( 'abcd-ef-01' , HEX(CURRENT DATE) , 'abcdefgh' ) )

Marcus_A · #4 (**permalink**) 10-13-10, 23:23

You forgot the ye olde substring function:

first day of current month ===> substr(char(current_date,iso),1,8)||'01'

goldfishhh · #5 (**permalink**) 10-14-10, 09:37

Quote:

Originally Posted by maxwellhouse

I am a SQL Server guy, was hoping someone could help me accomplish the following using DB2.

The below query works fine in SQL Server. I am having difficulties doing something similar in DB2.
Specially, I am trying to pull all records out of a DB2 table
WHERE
TRAN_TS >= first day of previous month
AND
TRAN_TS < first day of current month

WORKING SQL CODE:

select * from atable
where
TRAN_TS >= DateAdd(month, DateDiff(month, 0, getdate())-1, 0)
and
TRAN_TS < DateAdd(month, DateDiff(month, 0, getdate()), 0)

Does anyone know how to do the same in DB2?

Thanks

You could also do this (I adapted it for your field)

Code:

,CASE WHEN (YEAR(TRAN_TS 12+                        
            (MONTH(TRAN_TS ))) =                       
(                                                             
SELECT (YEAR(CURRENT DATE)*12+                           
(MONTH(CURRENT DATE) -1))                                
FROM SYSIBM.SYSDUMMY1                                         
)                                                             
       THEN 1 ELSE 0 END             -- IS_IN_PRIOR_MNTH_NBR,     
,CASE WHEN (YEAR(TRAN_TS 12+                        
            (MONTH(TRAN_TS ))) =                       
(                                                             
SELECT (YEAR(CURRENT DATE)*12+                           
(MONTH(CURRENT DATE) ))                                
FROM SYSIBM.SYSDUMMY1                                         
)                                                             
       THEN 1 ELSE 0 END             -- IS_IN_CURRENT_MNTH_NBR,

maxwellhouse · #6 (**permalink**) 10-14-10, 10:48

Good stuff guys....!

Should "MONTHs" really read "MONTH"?

LAST_DAY(CURRENT DATE) + 1 DAY - 2 MONTHs

tonkuma · #7 (**permalink**) 10-14-10, 12:46

Quote:

Should "MONTHs" really read "MONTH"?

Both are OK.

IBM DB2 9.7 for Linux, UNIX, and Windows SQL Reference, Volume 1
Chapter 2. Language elements ---> Expressions ---> Datetime operations and durations ---> Durations

Quote:

.....

A labeled duration represents a specific unit of time as expressed by a number
(which can be the result of an expression) followed by one of the seven duration
keywords: YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS, or
MICROSECONDS. (The singular form of these keywords is also acceptable: YEAR,
MONTH, DAY, HOUR, MINUTE, SECOND, and MICROSECOND.) .....

I like to use plural form for duration, except a number is a literal one(i.e. 1).
One reason is that the principal clearly distinguish between durations and functions.
Like this:

Quote:

CURRENT DATE - ( DAY(CURRENT DATE) - 1 ) DAYs

DAY is a function.
DAYs is a duration.

Lenny77 · #8 (**permalink**) 10-14-10, 12:55

You have to change:

Quote:

TRAN_TS >= first day of previous month
AND
TRAN_TS < first day of current month

to

Code:

TRAN_TS between 
last_day(current date - 2 month) + 1 day 
AND
last_day(current date - 1 month)

Lenny

maxwellhouse · #9 (**permalink**) 10-14-10, 13:13

Lenny, yours is pretty easy to understand.
Much thanks!

Lenny77 · #10 (**permalink**) 10-14-10, 13:25

It has to work, too !

tonkuma · #11 (**permalink**) 10-19-10, 17:43

DB2 9.7 for LUW supports TRUNC function for datetime expression, like this .....

Code:

TRAN_TS between 
TRUNC(current date - 1 MONTH , 'MM')
AND
last_day(current date - 1 month)

Internet Services

Web Development

Digital Marketing

Consumer Tech