Date variables/Functions for DB2 in SQL DTS

VegaLA · #1 (**permalink**) 03-15-10, 15:17

Good day everyone.
I have a SQL DTS package that runs the following code and pulls the Data from an AS400 table to a SQL table. This all works rather well but i'd rather use a date function to determine the Data rather then havign to change the hard coded part on a monthly basis. The criteria looks like this:-

WHERE
(AS400Table.AS400Field BETWEEN 20100301 AND 20100331)
or
(AS400Table.AS400Field BETWEEN 20100216 and 20100315)

In SQL if I wanted to run this the WHERE clause would look like this:-

WHERE
((AS400Table.AS400Field BETWEEN dateadd(mm, datediff(mm, 0, getdate()) - 2, 0) AND SELECT dateadd(mm, datediff(mm, 0, getdate()) +1, -1))
or
((AS400Table.AS400Field BETWEEN SELECT dateadd(mm, datediff(mm, 0, getdate()) - 1, +15) AND SELECT dateadd(mm, datediff(mm, 0, getdate()) , +14))

Since the data is being pulled from AS400 the syntax will not work, can anyone assist me in automating this Data range so I can pull the correct Data from AS400 please?

Thanks in advance,
Mitch....

n_i · #2 (**permalink**) 03-15-10, 15:40

something like this:

Code:

between 
  current_date - day(current_date) + 1 days
and
  current_date + 1 month - day(current_date + 1 month) days

I guess you can figure out the other one by yourself.

VegaLA · #3 (**permalink**) 03-15-10, 19:58

Thanks Nick.
I edited the DTS SQL query using BETWEEN current_date - day(current_date) + 1 days AND current_date + 1 month - day(current_date + 1 month) days) and the error message I get is as follows:

SQL0182 - A Date, time , or timestamp expression not valid.

I'm not sure if this is because AS400 keeps the date as '20100216' ?

Any ideas?

Thanks,
Mitch....

n_i · #4 (**permalink**) 03-15-10, 20:47

Try "current date" instead of "current_date"

n_i · #5 (**permalink**) 03-15-10, 20:49

By the way, here's the manual: iSeries Information Center

tonkuma · #6 (**permalink**) 03-15-10, 21:44

Quote:

WHERE
(AS400Table.AS400Field BETWEEN 20100301 AND 20100331)
or
(AS400Table.AS400Field BETWEEN 20100216 and 20100315)

What is a data type of AS400Table.AS400Field?

Try
BETWEEN current_date - (day(current_date) + 1) days AND current_date + 1 month - day(current_date + 1 month) days

If you want this month(e.g. BETWEEN '2010-03-01' AND '2010-03-31'), try
BETWEEN current_date - (day(current_date) - 1) days AND current_date + 1 month - day(current_date + 1 month) days

VegaLA · #7 (**permalink**) 03-19-10, 19:53

Thanks guys.
Just checked the table out on AS400 and just discovered that the field in that table is not in fact date type but 'Numeric Zoned' so... i'll have to rethink my approach.
Sorry it took me so long to reply, been workign elsewheer but really appreciate your help!

Mitch......