Hi Rajesh,
The format that Optim uses for Date colum is driven by the date format in your source database. Either you can modify the date format in your source DB or you can try following manipulation via Optim to reformat the date/timestamp column. I have not implemented this concept before, so it may or may not work :)
Suppose you have a table "A" with 2 columns
- Some_char defined as VARCHAR/STRING
- Some_date defined as DateTimestamp
Step1: create a view "A_view"
- Some_char defined as VARCHAR/STRING
- Some_date defined as VARCHAR/STRING
Step2: In your table map for convert, use "A_view" as destination table
Step3: Create a column map procedure to perform dummy aging and change date format. Apply the procedure on "Some_char" column and inside read/write to "Some_date" column
pro=age, mtd=rep, month=-12, year=1, srcdf="%MM/%DD/%YYYY %HH:%MI:%SS:%FFFFFF",
dstdf="%YYYY-%MM-%DD %HH:%MI:%SS", flddef1=(name="Some_char", dt=DATETIME_WSZ)
I believe %YYYY-%MM-%DD %HH:%MI:%SS format will work for inserts in BigQuery.
------------------------------
Thanks,
Ankur Agrawal
------------------------------
Original Message:
Sent: Thu February 09, 2023 07:37 PM
From: rajesh guttikonda
Subject: IBM Optim Convert request change date column format
HI ALl,
I am running a simple convert request and generating data in CSV format. Then I am using the generated CSV file to load into Big Query Database.
while generating CSV file on Optim, Optim generates date format as M/DD/YYYY and timestamp as M/DD/YYYY HH:MM:SS.000000 AM
where as BQ needs data as YYYY-MM-DD and timestamp as YYYY-MM-DDThh:mm:ss.
is there any easy way on Optim to change the format of date instead of convert it to string and modify the format ?
help will be much appreciated .
------------------------------
rajesh guttikonda
------------------------------