Yesterday's Date Help!

cyclogenisis · Post by **cyclogenisis** » Wed Dec 09, 2015 11:46 am

I'm running a shell script through the EXEC stage. The command requires yesterday's date in YYYYMMDD format in part of the shell script. How can I get a user variable/job parameter with yesterdays date in the format so it can be used in this stage?

chulett · Post by **chulett** » Wed Dec 09, 2015 11:53 am

Just want to make sure I understand - you need to get yesterday's date from the UNIX command line?

If so, then this is the trick you'll find if you google:

Code: Select all

`TZ=MST+24 date +%Y%m%d`

Use your timezone. If that's not what you need then please clarify.

cyclogenisis · Post by **cyclogenisis** » Wed Dec 09, 2015 1:14 pm

No as a variable or parameter in datastage. That can be used in other stages.

chulett · Post by **chulett** » Wed Dec 09, 2015 1:31 pm

Still not clear... if it needs to be a job parameter so it can be "used in other stages" who/what is responsible for fetching or setting the value? Does "other stages" mean in a Sequence job? Other stages in a PX job? And what in the heck does this have to do with the script?

Easy enough to write a custom routine to get the system date, subtract 1 from it and then format it into a YYYYMMDD string in a Routine Activity stage. Then a sequence job can pass the value downstream to a job parameter in a Job Activity stage that runs the job that needs it.

If I'm still not helping, you need to be as specific as possible as to what it is you need so I can stop floundering around here.

FranklinE · Post by **FranklinE** » Wed Dec 09, 2015 1:44 pm

Our "yesterday" date requirement is for the previous business day. I solve this simply by putting the date in a file. I have an after-job routine that updates the file with "today's" date to be used the next time the jobs run. I use echo and the parameter that has the current date in it.

To read the date, I use the Execute Command sequence stage to run a "cat" command on the file, then a user variable stage to place the CommandOutput into a variable I can pass to the parallel job. I use the following in the derivation for the user variable to avoid control character problems:

Code: Select all

Convert(@FM,"", GET_PREV_DATE.$CommandOutput)

cyclogenisis · Post by **cyclogenisis** » Wed Dec 09, 2015 1:47 pm

Will try to be more clear.

Currently, I have a ExecCommand stage which executes a simple shell command. Here is an example for test purposes:

echo "#UserVariables_Activity_5.Date#,2,3,4,5,6" > /somepath/test_1.csv

I would like the parameter or variable (it doesn't matter which) #UserVariables_Activity_5.Date# to be yesterday's date in YYYYMMDD format. Is the way you described in the last post the easiest way of getting this done, when you speak of custom routine are you talking about the routine stage? Sorry I am newer to datastage and working on my first project.

ray.wurlod · Post by **ray.wurlod** » Wed Dec 09, 2015 1:47 pm

In the controlling sequence you can use

Code: Select all

Oconv(Date() - 1, "DYMD[4,2,2]" : @VM : "MCN")

Date() always uses the current date. If you prefer, use system variable @DATE instead, which is set to the job start date.

cyclogenisis · Post by **cyclogenisis** » Wed Dec 09, 2015 2:25 pm

Ray you are awesome. Thank you very much for your help, if I was a bit more clear to start we could have saved ourselves some typing.

chulett · Post by **chulett** » Wed Dec 09, 2015 4:56 pm

I never got a chance to be awesome.

cyclogenisis · Post by **cyclogenisis** » Wed Dec 09, 2015 11:01 pm

You were equally as awesome. Thanks very much for your time!