passing date parameter

Krazykoolrohit · Post by **Krazykoolrohit** » Fri Apr 13, 2007 10:13 am

I tried the search but couldnt find anything to help me.

I am passing the following SQL in the DB2 API. i need to extract data for the last week

Code: Select all

Z_PLCMT between 'DATE('#Week_Start_Date#')' || '-00.00.00.000000' and 'DATE('#Week_End_Date#')' || '-00.00.00.000000'

the values for Week_Start_Date being passed = 14347 and for Week_End_Date = 14341

the error message: SQL0104N An unexpected token "14347" was found following "". Expected tokens may include: "+ - AND". SQLSTATE=42601

Can you please point out where i am doing anything wrong in the syntax?

Krazykoolrohit · Post by **Krazykoolrohit** » Fri Apr 13, 2007 10:18 am

the following worked

Code: Select all

Z_PLCMT between char(DATE(#Week_Start_Date#)) || '-00.00.00.000000' and char(DATE(#Week_End_Date#)) || '-00.00.00.000000'

but have another query now.

DATE(14347) is not giving the output as '04-06-2007' instead its just passing 14347.

us1aslam1us · Post by **us1aslam1us** » Fri Apr 13, 2007 1:51 pm

Is it the Julian Date? I don't see any date function which will convert an Integer representation to date format using DATE function.I am not sure but might be you can do something like this:

Code: Select all

 DATE('Jan 1 0001' + 14347 DAYS)

ray.wurlod · Post by **ray.wurlod** » Fri Apr 13, 2007 3:43 pm

14347 could well be a DataStage internal format (12 Apr 2007). Try applying an appropriate Oconv() function to get it into the format required in the SQL.

Krazykoolrohit · Post by **Krazykoolrohit** » Mon Apr 16, 2007 7:53 am

ray,

it doesnt accept oconv in SQLs. i have worked it out and made them as a parameter to the jobs. now i need to capture current date and date of a week before in the job parameters.

us1aslam1us · Post by **us1aslam1us** » Mon Apr 16, 2007 8:24 am

Unless we know what that number exactly signify it will be like hitting in the arrow in the dark.

Krazykoolrohit · Post by **Krazykoolrohit** » Mon Apr 16, 2007 1:32 pm

ok. lets start from begining. how do i capture yesterdays date and pass into a job using job parameters?

us1aslam1us · Post by **us1aslam1us** » Mon Apr 16, 2007 1:34 pm

currdate()-1 or sysdate()-1 just without even using parameters if you are extracting the data on daily basis.

Krazykoolrohit · Post by **Krazykoolrohit** » Mon Apr 16, 2007 2:24 pm

now i need to get this sysdate to the following format before passing into the SQL.

Z_PLCMT between '2007-04-06-00.00.00.000000' and '2007-04-12-00.00.00.000000'

so i need systime.or equivalent.

Krazykoolrohit · Post by **Krazykoolrohit** » Mon Apr 16, 2007 2:29 pm

us1aslam1us wrote:currdate()-1 or sysdate()-1 just without even using parameters if you are extracting the data on daily basis.

the SQL is aborting when i give the following SQL. i am extracting data from DB2. wud i need to format the sysdate output to DB2 format.

am using this as a filter in the SQL.

Code: Select all

 between sysdate()-1 and sysdate()-5

ray.wurlod · Post by **ray.wurlod** » Mon Apr 16, 2007 3:09 pm

The word you were looking for is "would".

Since sysdate - 5 is before sysdate - 1 try reversing the order in the BETWEEN constraint.

Also I'm not sure that sysdate is a function. But I'm not in a position to check right now. If it's not, you need to lose the parentheses from it - that is, use SYSDATE rather than SYSDATE().

ds_developer · Post by **ds_developer** » Mon Apr 16, 2007 3:20 pm

I avoid using sysdate in a source query since I am likely to have to reload data for a specific date in the past. I use a job control job to calculate the date then pass it to the real job using DSSetParam. The job control job has a parameter which is normally left blank. This causes the job control code to calculate yesterday's date. When this parameter contains a date (like 20070411) that date is used.

Just an idea.
John

us1aslam1us · Post by **us1aslam1us** » Mon Apr 16, 2007 4:41 pm

For DB2, It should be

Code: Select all

 CURRENT DATE

orelse you can use

Code: Select all

CURRENT TIMESTAMP

Also make about valid date range, typically it
is from 0001-01-01 to 12-31-9999

DSguru2B · Post by **DSguru2B** » Mon Apr 16, 2007 5:16 pm

Pay attention to ds_develper's suggestion. A very valid point.

Krazykoolrohit · Post by **Krazykoolrohit** » Tue Apr 17, 2007 2:53 pm

ds_developer wrote:I avoid using sysdate in a source query since I am likely to have to reload data for a specific date in the past. I use a job control job to calculate the date then pass it to the real job using DSSetParam. The job control job has a parameter which is normally left blank. This causes the job control code to calculate yesterday's date. When this parameter contains a date (like 20070411) that date is used.

Just an idea.
John

Joihn, can you please elaborate? i dont know what the job control parameter should be.