timestamp

[us1aslam1us]

Hi all,

i need some help in implementing this requirement:

I am loading timestamp field as varchar in the sequential file.And parameterizing this date so that i can use this date for some comparision in the next job run for pulling the data from source.But my worry is that,can i compare this parameter with timestamp from the source?

In SQL i want to use like this

Create_Date > Parm_Date

Any suggestions please

Thanks
sam

[roy]

Hi,
You'll need to convert the type to the source type using the conversion functions of the Modify stage or convert the DB type from timestamp to varchar as you did for the sequential file.

Here are some conversion functiuons for the modify stage:

Code: Select all

timestamp_from_string (string, timestamp)
timestamp_from_ustring (ustring, timestamp)
ustring_from_timestamp (timestamp, ustring)
string_from_timestamp (timestamp, string)

IHTH,

[kumar_s]

Also use

Code: Select all

julian_day_from_date (date_from_timestamp [date_format])

to do exact numerical comparision using modify stage.

[us1aslam1us]

Hi

I understood the idea but could you guys tell me how to use a parameter the parameter in the SQL.

I am having a .parm file in the following format

PARMDATE=2006-04-01 00:12:36

And I am trying to pull the data from one of the source table which is in sybase which is having three fields:

ID char
Create_Date timestamp
Desc_cfp char

I am using Sybase OCI stage and written the query like

select ID,Create_Date,Desc_cfp
from table
where Create_Date>#PARMDATE#

But i would like to know how i need to initialise this parameter in the parameter section and how to convert the field so that the SQL works fine.

Thanks and Appreciation
Sam

[ray.wurlod]

where Create_Date>'#PARMDATE#'

Initialize before invoking the job, either from a job sequence or job control routine, or when prompted for a value.

[us1aslam1us]

where Create_Date>'#PARMDATE#'

Initialize before invoking the job, either from a job sequence or job control routine, or when prompted for a value.

Hi Ray i tried this but my job is getting aborted and i am getting this warnings in director.

Initial_Extract,0: Warning: IDC_CARDS_VALID_INIT_EXTR.Initial_Extract: Sybase Server warning 249 (severity 16): Syntax error during implicit conversion of VARCHAR value '#DSCAPIOP_PARMDATE#' to a DATETIME field.
Initial_Extract,0: Operator terminated abnormally: received signal SIGSEGV
main_program: Step execution finished with status = FAILED.

Can you let me know how to convert that fields.

Thanks
sam

[rameshrr3]

Code: Select all

[quote]select ID,Create_Date,Desc_cfp 
from table 
where Create_Date>#PARMDATE# [/quote]

I would rewrite the query thus if i were using oracle

Code: Select all

select ID,Create_Date,Desc_cfp 
from table 
where Create_Date>TO_DATE(#PARMDATE#, 'mm/dd/yy')

im not sure what problem you are up against, but
i guess you need to use a sybase function that does date conversion in your query.the sybase refernce manuals may have the details.

Thanks
Ramesh[/quote]

[rameshrr3]

further, to assign a value to #PARMDATE#, you will have to introduce this job as a job activity stage in a sequencer and edit teh job activity stage, use a datastage function lile "Oconv(@DATE,"FORMAT_CODE") to pass a VARCHAR value to the #PARMDAT# parameter, the advantage here(using sequencer) is , you can use a function to assign the runtime value of a parameter ina job activity stage, especially helpful in date parameters.

This is something that you cannot do in the job properties parameter grid, where you necessarily have to hardcode a default value thats not dynamic.

HTH
Ramesh

[ray.wurlod]

Oconv() is not natively available in parallel jobs.

[us1aslam1us]

Hi

After using the convert function,still i am getting this error.I think the problem is not with the conversion but the way i am initialising the parameter.Can someone suggest me how to approach on these things:

1.How to initialize this job parameter? I had already created a job which is storing the PARMDATE in the parm file but i am not getting the idea like whether i need to initialise this PARMDATE again in the job properties or ....

2. How to convert the this Varchar field to 'Apr 21 2006 12:23:00' format in sybase? I used Convert(varchar(30),PARMDATE) but i am still facing the problem.

Any help is appreciated
Thanks
sam

[us1aslam1us]

Whenm i am hardcoding the date in the string format it is working fine but when i am trying to pass it through parameter as Varchar,it is giving me that error.Any views

Thanks
sam

[DSguru2B]

Are you passing the parameter value with single quotes.
As in 'Apr 21 2006 12:23:00' and not Apr 21 2006 12:23:00[/b]

[us1aslam1us]

Are you passing the parameter value with single quotes.
As in 'Apr 21 2006 12:23:00' and not Apr 21 2006 12:23:00[/b]

I am passing it as '2006-01-01 12:23:00'.

Thanks
sam

[DSguru2B]

To convert it to Varchar, try something like

Code: Select all

cast(#PARMDATE# as varchar(30))

[us1aslam1us]

To convert it to Varchar, try something like

Code: Select all

cast(#PARMDATE# as varchar(30))

I need to convert this varchar to datetime.

Thanks
sam