AS400 & filter by date

#31

Tomas Cerny wrote:

Quote:

Hello guys, thank you for posts.

Rick, thanks for CURRENT_DATE command, it helped, but we are not done
yet. Maybe I haven't expressed myself correctly, what we exactly need:

We need to create a DTS package that imports data from AS400 tables
(DB2) via ODBC driver (made by IBM - that from iSeries driver) into
MS SQL 2000 server.

We need to filter records based on range of dates (for example from
01/03/2008 to 31/03/2008, assuming this date format is DD/MM/YYYY).

The problem we face now is, that AS400 (DB2) natively holds date type
format as number. In my example 20080301 and 20080331.

Incorrect actually. The AS400 has had "real" DataTypes for Date, Time, and
Timestamp for a long time now. You just have a table that was designed to
hold dates as numeric values which means you must filter them as numeric
values and not as dates.

Quote:

So we need a construction, passable through ODBC driver to compare for
example CURRENT_DATE (2008-03-01 00:00:00) with 20080301.

Replace(Char(CURRENT_DATE),'-','')

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

#32

Tomas Cerny wrote:

Quote:

Hello guys, thank you for posts.

Rick, thanks for CURRENT_DATE command, it helped, but we are not done
yet. Maybe I haven't expressed myself correctly, what we exactly need:

We need to create a DTS package that imports data from AS400 tables
(DB2) via ODBC driver (made by IBM - that from iSeries driver) into
MS SQL 2000 server.

We need to filter records based on range of dates (for example from
01/03/2008 to 31/03/2008, assuming this date format is DD/MM/YYYY).

The problem we face now is, that AS400 (DB2) natively holds date type
format as number. In my example 20080301 and 20080331.

Incorrect actually. The AS400 has had "real" DataTypes for Date, Time, and
Timestamp for a long time now. You just have a table that was designed to
hold dates as numeric values which means you must filter them as numeric
values and not as dates.

Quote:

So we need a construction, passable through ODBC driver to compare for
example CURRENT_DATE (2008-03-01 00:00:00) with 20080301.

Replace(Char(CURRENT_DATE),'-','')

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

#33

Tomas Cerny wrote:

Quote:

Hello guys, thank you for posts.

Rick, thanks for CURRENT_DATE command, it helped, but we are not done
yet. Maybe I haven't expressed myself correctly, what we exactly need:

We need to create a DTS package that imports data from AS400 tables
(DB2) via ODBC driver (made by IBM - that from iSeries driver) into
MS SQL 2000 server.

We need to filter records based on range of dates (for example from
01/03/2008 to 31/03/2008, assuming this date format is DD/MM/YYYY).

The problem we face now is, that AS400 (DB2) natively holds date type
format as number. In my example 20080301 and 20080331.

Incorrect actually. The AS400 has had "real" DataTypes for Date, Time, and
Timestamp for a long time now. You just have a table that was designed to
hold dates as numeric values which means you must filter them as numeric
values and not as dates.

Quote:

So we need a construction, passable through ODBC driver to compare for
example CURRENT_DATE (2008-03-01 00:00:00) with 20080301.

Replace(Char(CURRENT_DATE),'-','')

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

AS400 & filter by date

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: AS400 & filter by date - 04-07-2008 , 01:06 PM

Re: AS400 & filter by date - 04-07-2008 , 01:06 PM

Re: AS400 & filter by date - 04-07-2008 , 01:06 PM