Pass yesterday's date as parameter

opdas · Post by **opdas** » Wed Mar 01, 2006 6:31 am

Hi,

I have a situation where I have to extract data from a oracle table which are generated daily with name as "table_mmddyyyy" so if I can pass yesterday's date as parameter in the extarction job then I can select the table which is created yesterday.

How can I pass that automatically at run time?

Pl help.

Regards
Om

ray.wurlod · Post by **ray.wurlod** » Wed Mar 01, 2006 6:35 am

If you're passing a parameter to the job you have to pass it from somewhere - perhaps a job sequence, perhaps a shell script. It's there that you generate yesterday's date. In a job sequence a suitable expression is

Code: Select all

Oconv(@DATE - 1, "D-YMD[4,2,2]")

(Adjust the specification to suit requirements.)

opdas · Post by **opdas** » Wed Mar 01, 2006 6:41 am

ray.wurlod wrote:If you're passing a parameter to the job you have to pass it from somewhere - perhaps a job sequence, perhaps a shell script. It's there that you generate yesterday's date. In a job sequence a suitable expression is
Code: Select all
Oconv(@DATE - 1, "D-YMD[4,2,2]")
(Adjust the specification to suit requirements.)

Thanks for the prompt reply ray,
I have a shell script written which can generate yesterday's date in the format I want. How do I pass it in parameter?

Regards
Om

ray.wurlod · Post by **ray.wurlod** » Wed Mar 01, 2006 6:44 am

Code: Select all

dsjob -server <host> -user <userID> -password <passwd> -run -jobstatus -param name=value <projectname> <job>

You can have as many -param name=value options as needed.

kumar_s · Post by **kumar_s** » Wed Mar 01, 2006 6:55 am

Create a parameter for table name.
In Job Sequence Value expression where parameters value is passed, use TAB:Oconv(Date()-1,"DMDY[2,2,4]")
Where TAB is the parameter for the table name.

kumar_s · Post by **kumar_s** » Wed Mar 01, 2006 6:58 am

Oops a late reply

opdas · Post by **opdas** » Mon Mar 06, 2006 4:47 am

kumar_s wrote:Oops a late reply

Hi Kumar,
Your solution looked intresting, but geting the following date format but I dont want the space in between.

"03 05 2006" I want>> "03052006"

Regards
Om

ameyvaidya · Post by **ameyvaidya** » Mon Mar 06, 2006 5:28 am

Hi Om,

Wrap Kumar's Function call in ereplace like:

Code: Select all

EREPLACE(Oconv(Date()-1,"DMDY[2,2,4]"),' ','')

to replace all spaces to Empty Strings.

opdas · Post by **opdas** » Mon Mar 06, 2006 6:05 am

ameyvaidya wrote:Hi Om,

Wrap Kumar's Function call in ereplace like:
Code: Select all
EREPLACE(Oconv(Date()-1,"DMDY[2,2,4]"),' ','')
to replace all spaces to Empty Strings.

Done...
Thanks a Lot !

ray.wurlod · Post by **ray.wurlod** » Mon Mar 06, 2006 7:20 am

More efficient is

Code: Select all

Oconv(@DATE-1, "D-YMD[4,2,2]" : @VM : "MCN")

(I assume this expression is in a job sequence; Oconv() is not available in parallel jobs but is available in job sequences.