Subract the date with 365 days

[mdtauseefhussain]

HI! All

I have a date column UDT_DATE ,iam using this date to calculate the flag
My condition
If UDT_DATE>=(last 12 months ) Then 'Y' ELse 'N' .So i used this way

If UDT_DATE>= Sysdate-365 Then 'Y' ELse 'N' .I bring the Sysdate form the source level .The problem when i tried to subract the sysdate with 365 i was not able to do so can any one help me in this


Thanks in advance

[ArndW]

You can use the DataStage variable @DATE to get the current date in internal numeric form. I don't know what format either your UDT_DATE or "sysdate" is in, so can't comment on why it isn't working. Is the "sysdate" a job parameter?

[mdtauseefhussain]

Both te formats are in YYYY-MM-DD HI24:MI:SS ,iam not passing Sysdate as a parameter ,instead iam using

SELECT TO_CHAR(UDT_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_CHAR(DATETIME_ADDED, 'YYYY-MM-DD HH24:MI:SS'),
to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LAST_UDT
FROM edw_product_d
and pulling the sysdate column in transformer an then iam trying to subract with 365 ,i tried using @DATE system variable its giving date in this format eg:14023 ,i didnt understand this date do i need to use any othe statements along with @DATE ,can we subract 365-@DATE

[balajisr]

To subtract 365 days from the current date use:

@Date - 365

@DATE returns date in internal format. You need to use OConv to change it in your format.

E.g OConv(@Date,"Date Format")

[mdtauseefhussain]

Thanks for your help

[rwierdsm]

SELECT TO_CHAR(UDT_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_CHAR(DATETIME_ADDED, 'YYYY-MM-DD HH24:MI:SS'),
to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LAST_UDT
FROM edw_product_d

Rather than do date manipulation in DS, (which I avoid at all costs!), do it in your SQL, i.e.

Code: Select all

SELECT TO_CHAR(UDT_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_CHAR(DATETIME_ADDED, 'YYYY-MM-DD HH24:MI:SS'), 
to_char(SYSDATE -365, 'YYYY-MM-DD HH24:MI:SS')  AS LAST_UDT_MINUS_365 FROM edw_product_d

Using my example above, you would have to change the name of your input column.

Hope this helps,

Rob W

[mdtauseefhussain]

I did that before but i was getting null value in datastage

[DSguru2B]

My advice do it in the transformer. Your column values are timestamps. Just extract the date part, Iconv it and then subtract. Also if you do the manipulation in the transformer, that also makes your code flexible to be moved between different environments/databases.
Regards,