|
View previous topic :: View next topic
|
| Author |
Message |
rajendraa
New User
Joined: 22 Dec 2006
Posts: 7
Location: Bangalore
|
|
|
|
Hi,
I have a requirement to unload data from DB2 table to flat file using batch db2 unload utility
The file length is 50 bytes
Following are the columns that I will be downloading
| Code: |
Column Name: Office_code Start_Date
-------------------------------------------
Declaration: CHAR(3) DATE
-------------------------------------------
row1 : USA 2010-05-30
row2 : IND 2010-07-17 |
Data should be as follows in the flat file.
| Code: |
Office_code PIC X(03)
Filler PIC X(27) VALUE SPACES
Date PIC X(08) (Format of YYYYMMDD)
Filler PIC X(12) VALUES SPAVES |
| Code: |
----+----1----+----2----+----3----+----4----+----5
***************************** Top of Data ********
USA 20100530
IND 20100717 |
SELECT office_code
,CHAR(SPACE(27))
,start_date
,CHAR(space(12))
FROM TABLE1
Can someone help me how to convert date from YYYY-MM-DD format to YYYYMMDD format while downloading from DB2 to flat file?
Thank you |
|
| Back to top |
|
 |
haimzeevi
New User
Joined: 01 Mar 2010
Posts: 27
Location: Israel
|
|
|
|
Hi,
Just add a SORT step, SYNCSORT or DFSORT, according your site, then use OUTREC statement to re-format the date-fields.
Works, fast & efficient.
If you have SYNCSORT, you can issue a direct SELECT from DB2, then use OUTREC to format the date-fields.
Haim Zeevi |
|
| Back to top |
|
|
rajendraa
New User
Joined: 22 Dec 2006
Posts: 7
Location: Bangalore
|
|
|
|
Thanks haimzeevi
I wanted to avoid sort step if the date conversion can be handeled in the unload query itself.
We can use replace and trim options in the unload select statement, but wanted to check if there is any better of doing this. |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
| If a local date format that suits your needs hasn't been implemented at your installation, I would suggest you look into selecting YYYY, MM, and DD separately with suitable date functions. |
|
| Back to top |
|
|
rajendraa
New User
Joined: 22 Dec 2006
Posts: 7
Location: Bangalore
|
|
|
|
Thanks Kjeld.
I tried as per your suggestion, but the zeros is getting replaced with spaces
Below is the query that was used
Select
CAST(YEAR(START_DATE) AS CHAR(4))
CAST(MONTH(START_DATE) AS CHAR(2))
CAST(DAY(START_DATE) AS CHAR(2))
from TABLE1
Value in DB2 table is 2010-05-30
Value in flat files after unload 2010 530
But I am looking for data as 20100530 in the flat file.
For month I am getting ' 5' instead of '05'. Is there a way to handle this.
Please advice. |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| rajendraa wrote: |
I tried as per your suggestion, but the zeros is getting replaced with spaces
Below is the query that was used
Select
CAST(YEAR(START_DATE) AS CHAR(4))
CAST(MONTH(START_DATE) AS CHAR(2))
CAST(DAY(START_DATE) AS CHAR(2))
from TABLE1
Value in DB2 table is 2010-05-30
Value in flat files after unload 2010 530
But I am looking for data as 20100530 in the flat file.
For month I am getting ' 5' instead of '05'. Is there a way to handle this.
Please advice. |
Yes.
OK, I'll take pity on one of such limited ability: use the SQL TRANSLATE function. In fact, since you're evidently incapable of using Google, I'll even provide the link for you. |
|
| Back to top |
|
|
rajendraa
New User
Joined: 22 Dec 2006
Posts: 7
Location: Bangalore
|
|
|
|
I used the below query and it solved my problem. Thanks all.
Select
SUBSTR(CHAR(START_DATE),1,4)
SUBSTR(CHAR(START_DATE),6,2)
SUBSTR(CHAR(START_DATE),9,2)
from TABLE1 |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
two other ways to accomplish the same:
| Code: |
,char(varchar_format(timestamp(current date,'00.00.00'),'YYYYMMDD'),8)
,decimal(year(current date)*10000+month(current date)*100+day(current date),8,0) |
|
|
| Back to top |
|
|
|
|
