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 . 

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.

Another option is to CONVERT the date column in your Extract SQL. Not sure what the source data DBMS is, but most of them have ways to CONVERT date and time fields. 

A way to manipulate Optim to allow this is via a UNION ALL on the same table whereby the top part of the Union generates zero rows and the lower part is where you can manipulate the table columns and apply your WHERE conditions. We use the process outlined below to manipulate or null out columns from tables in an Access Definition. The format in the example below would be for SQL Server, but conceptually should work with other source DBMS. 

Hope this helps. 

-Keith 

Example SQL:

SELECT * 
FROM SOURCE_TABLE 
WHERE 1=0

UNION ALL

SELECT A.COLUMN_1
,A.COLUMN_2,
,CONVERT (A.DATE_COLUMN, VARCHAR, GETDATE(), #)      [# is the date format you're converting to; there are charts online listing options]
,A.COLUMN4
, etc.
FROM SOURCE_TABLE AS A
WHERE whatever criteria you specify

Thanks Ankur. I will try it out.

Hey Keith Tidball,

I agree with SQL part. I can replicate the same Union SQL command for my DB2 table which might solve my issue.

But where do we provide the this Union all SQL statement? Where in Optim AD?

Thanks Every one for your response. i am able resolve the issue by changing the date format in the data source.