Page 1 of 1
Subract the date with 365 days
Posted: Tue May 23, 2006 1:18 am
by 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
Posted: Tue May 23, 2006 1:35 am
by 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?
Posted: Tue May 23, 2006 2:36 am
by 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
Posted: Tue May 23, 2006 4:10 am
by 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")
Posted: Tue May 23, 2006 6:28 am
by mdtauseefhussain
Thanks for your help
Posted: Tue May 23, 2006 7:29 am
by rwierdsm
mdtauseefhussain wrote:
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
Posted: Tue May 23, 2006 7:55 am
by mdtauseefhussain
I did that before but i was getting null value in datastage
Posted: Tue May 23, 2006 8:22 am
by 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,