timestamp
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
timestamp
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
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
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:
IHTH,
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,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Also use to do exact numerical comparision using modify stage.
Code: Select all
julian_day_from_date (date_from_timestamp [date_format])
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
Hi Ray i tried this but my job is getting aborted and i am getting this warnings in director.ray.wurlod wrote:where Create_Date>'#PARMDATE#'
Initialize before invoking the job, either from a job sequence or job control routine, or when prompted for a value.
Can you let me know how to convert that fields.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.
Thanks
sam
Code: Select all
[quote]select ID,Create_Date,Desc_cfp
from table
where Create_Date>#PARMDATE# [/quote]
Code: Select all
select ID,Create_Date,Desc_cfp
from table
where Create_Date>TO_DATE(#PARMDATE#, 'mm/dd/yy')
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]
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
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
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
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
To convert it to Varchar, try something like
Code: Select all
cast(#PARMDATE# as varchar(30))
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
I need to convert this varchar to datetime.DSguru2B wrote:To convert it to Varchar, try something likeCode: Select all
cast(#PARMDATE# as varchar(30))
Thanks
sam