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
Subract the date with 365 days
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 38
- Joined: Mon Feb 27, 2006 10:34 pm
- Location: Chennai
- Contact:
Subract the date with 365 days
Mohammed Tausif Hussain Sheikh
Cognizant technologies,Perungudi
Chennai
Cognizant technologies,Perungudi
Chennai
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 38
- Joined: Mon Feb 27, 2006 10:34 pm
- Location: Chennai
- Contact:
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
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
Mohammed Tausif Hussain Sheikh
Cognizant technologies,Perungudi
Chennai
Cognizant technologies,Perungudi
Chennai
-
- Participant
- Posts: 38
- Joined: Mon Feb 27, 2006 10:34 pm
- Location: Chennai
- Contact:
-
- Premium Member
- Posts: 209
- Joined: Fri Jan 09, 2004 1:14 pm
- Location: Toronto, Canada
- Contact:
Rather than do date manipulation in DS, (which I avoid at all costs!), do it in your SQL, i.e.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
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
Hope this helps,
Rob W
Rob Wierdsma
Toronto, Canada
bartonbishop.com
Toronto, Canada
bartonbishop.com
-
- Participant
- Posts: 38
- Joined: Mon Feb 27, 2006 10:34 pm
- Location: Chennai
- Contact:
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,
Regards,
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.