InfoSphere Optim

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 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 . 

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

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 . 

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

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 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 .