We help IT Professionals succeed at work.

DB2 SYntax

756 Views
Last Modified: 2013-11-10
I am trying to write a query from SSIS to pull data from DB2. I know that db2 does not like joins and I need to join into another table to pull the information by date. Below is an example of the query that i am pulling that works for SQL Server. Does someone know the correct syntax for db2.

SELECT     *
FROM        t1 INNER JOIN
                     t2 ON t1.emp_ID = t2.empid_ID
WHERE     (t2.END_DT BETWEEN DATEADD(dd, - (DAY(DATEADD(mm, 1, GETDATE())) - 1), DATEADD(mm, 0, GETDATE())) AND
                      DATEADD(dd, - DAY(DATEADD(m, 1, GETDATE())), DATEADD(m, 1, GETDATE())))

Thanks
Comment
Watch Question

It's not you JOIN.  It's your DATE stuff.

See http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html

More importantly, check what the data looks like in the DB2 system.  In many cases, DB2 databases are using some evil-looking string of numbers like 20091112122633.
explain in words what you are trying to filter with the dates and i'll give you the db2 version of it
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:

>>  I know that db2 does not like joins

As far as I know, IBM invented them.  SQL as we know it was offered with DB2 long before any other DBMS.

I like k_murli_kris...'s solution, though you might use the value of CURRENT DATE instead of CURRENT TIMESTAMP.


Kent

Author

Commented:
I did not mean to offend with my statement regarding joins. I meant that it does not like the microsoft syntax. I resolved that issue by using an implicit join which seems to work however, I still get an error on the date statement whether I use CURRENT DATE or CURRENT TIMESTAMP. The error that I am getting is

"An expression with a datetime value or a labeled duration is not valid."
what the exact statement you are using?
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Are you using the SQL that k_murli_kris... provided?  It might need just a bit of cleanup, but the idea is correct.

SELECT     *
FROM        t1 INNER JOIN
                     t2 ON t1.emp_ID = t2.empid_ID
WHERE     t2.END_DT
BETWEEN
(CURRENT TIMESTAMP - (DAY(CURRENT TIMESTAMP + 1 MONTHS) - 1) DAYS)
AND
(CURRENT TIMESTAMP + 1 MONTHS - DAY(CURRENT TIMESTAMP));
what data type is your DB2 database using for its dates?  If numeric, what is the format in use?

I realize DB2 has some modern date types.  But many applications still use some rather archaic types instead for dates.

Author

Commented:
The datatype is Timestamp.

Author

Commented:
Would this work?

BETWEEN
(CURRENT TIMESTAMP - (DAY(CURRENT TIMESTAMP) - 1) DAYS)
AND
(CURRENT TIMESTAMP - (DAY(CURRENT TIMESTAMP)) DAYS);
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi earngreen,

Help us that aren't as strong with the SQL functions.  :)  What are the bounds (the endpoints of the BETWEEN filter)?

Kent
should work

Author

Commented:
Hi earngreen,

Help us that aren't as strong with the SQL functions.  :)  What are the bounds (the endpoints of the BETWEEN filter)?

Kent

Sorry, I am looking for BETWEEN the first day of the month and the Last day of the month.
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi earngreen,

You probably don't care about TIME then, just the date.  You can certainly calculate the endpoints with similar SQL, but easiest is to just test the year and month.

SELECT     *
FROM        t1 INNER JOIN
                     t2 ON t1.emp_ID = t2.empid_ID
WHERE     YEAR (t2.END_DT) = YEAR (CURRENT DATE)
  AND        MONTH (T2.END_DT) = MONTH (CURRENT DATE);



Kent
try

between date(year(current date)||'.'||month(current date)||'.01')
and
date(year(current date)||'.'||month(current date)||'.01') - 1 day
You can try this corrected  one:

SELECT     *
FROM        t1 INNER JOIN
                     t2 ON t1.emp_ID = t2.empid_ID
WHERE     t2.END_DT
BETWEEN
(CURRENT TIMESTAMP - (DAYS(CURRENT TIMESTAMP + 1 MONTHS) - 1) DAYS)
AND
(CURRENT TIMESTAMP + 1 MONTHS - DAYS(CURRENT TIMESTAMP + 1 MONTHS));
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.