Date conversation
Moderators: chulett, rschirm, roy
Date conversation
Hi,
I am having a paramter as 2007-01-01, when i run the job i need the following logic to be implemented
1 . select TO_DATE ('12/01/'||TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) -1),'MM/DD/YYYY')
from dual;
i.,e it should give me 01-DEC-06 as a result. (12/01 is a hardcode values)
2. select TO_DATE(TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'MM'))-1)||'/01/'||TO_CHAR(SYSDATE,'YYYY'),'MM
/DD/YYYY') from dual;
i.,e it should give me 01-FEB-07 (previous month first date)
Thanks,
I am having a paramter as 2007-01-01, when i run the job i need the following logic to be implemented
1 . select TO_DATE ('12/01/'||TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) -1),'MM/DD/YYYY')
from dual;
i.,e it should give me 01-DEC-06 as a result. (12/01 is a hardcode values)
2. select TO_DATE(TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'MM'))-1)||'/01/'||TO_CHAR(SYSDATE,'YYYY'),'MM
/DD/YYYY') from dual;
i.,e it should give me 01-FEB-07 (previous month first date)
Thanks,
I am still not able to understand whats the use of the parameter if your not using it for your transformations. Anywho, the following should work
For #1:
and for #2:
For the second query you have to check if present month is January, you will take the year, subtract 1 and then concatenate it with "-12-01".
NOTE: Use the above query in the initial values of stage variables. This way it will only be executed once.
For #1:
Code: Select all
StringToDate(DecimalToString(YearFromDate(CurrentDate())-1):"-12-01", "%dd-%MMM-%yy")
Code: Select all
If MonthFromDate(CurrentDate()) = 1 then StringToDate(DecimalToString(YearFromDate(CurrentDate())-1):"-12-01", "%dd-%MMM-%yy") else StringToDate(DecimalToString(YearFromDate(CurrentDate()):"-":DecimalToString(MonthFromDate(CurrentDate()) - 1):"-01", "%dd-%MMM-%yy")
NOTE: Use the above query in the initial values of stage variables. This way it will only be executed once.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Why not the same given query in a Database stage.
Code: Select all
select TO_DATE ('12/01/'||TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) -1),'MM/DD/YYYY')
from dual;
select TO_DATE(TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'MM'))-1)||'/01/'||TO_CHAR(SYSDATE,'YYYY'),'MM
/DD/YYYY') from dual;
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Re: Date conversation
This query will break every time its run in January.srini.dw wrote:2. select TO_DATE(TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'MM'))-1)||'/01/'||TO_CHAR(SYSDATE,'YYYY'),'MM
/DD/YYYY') from dual;
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Hi,
Thanks for the below derviations.
Code:
StringToDate(DecimalToString(YearFromDate(CurrentDate())-1):"-12-01", "%dd-%MM-%yyyy")
Code:
If MonthFromDate(CurrentDate()) = 1 then StringToDate(DecimalToString(YearFromDate(CurrentDate())-1):"-12-01", "%dd-%MMM-%yy") else StringToDate(DecimalToString(YearFromDate(CurrentDate()):"-":DecimalToString(MonthFromDate(CurrentDate()) - 1):"-01", "%dd-%mm-%yyyy")
But when i execute the above codes in transformer its giving me ******
So wat iam doing is i have 4 stage variables i.,e MM, YY, MMminusOne, YYminusOne (all integer type)
When i execute the below one its works (datatype date)
StringToDate(svYYminusOne:"-":"01-":"01")
but when i try to execute (datatype date) StringToDate(svYYminusOne:"-":DecimalToString(svMM):"01") its still giving ******
MM is MonthFromDate(currentdate()
YY is YearFromDate(currentdate()
Can anyone plz let me know why..
Thanks
Thanks for the below derviations.
Code:
StringToDate(DecimalToString(YearFromDate(CurrentDate())-1):"-12-01", "%dd-%MM-%yyyy")
Code:
If MonthFromDate(CurrentDate()) = 1 then StringToDate(DecimalToString(YearFromDate(CurrentDate())-1):"-12-01", "%dd-%MMM-%yy") else StringToDate(DecimalToString(YearFromDate(CurrentDate()):"-":DecimalToString(MonthFromDate(CurrentDate()) - 1):"-01", "%dd-%mm-%yyyy")
But when i execute the above codes in transformer its giving me ******
So wat iam doing is i have 4 stage variables i.,e MM, YY, MMminusOne, YYminusOne (all integer type)
When i execute the below one its works (datatype date)
StringToDate(svYYminusOne:"-":"01-":"01")
but when i try to execute (datatype date) StringToDate(svYYminusOne:"-":DecimalToString(svMM):"01") its still giving ******
MM is MonthFromDate(currentdate()
YY is YearFromDate(currentdate()
Can anyone plz let me know why..
Thanks
The format was messed up. Use the following:
For #1
For #2
The date format was giving issues and hence you were seeing all stars. I fixed the format. You will get date in YYYY-MM-DD format. If you want it in any other format, do the above in a stage variable and pass that variable to DateToString(<<stgvar>>, "your format")
Another problem was cause by MonthFromDate(). It was returning a single digit month where as two digit month (%mm) is required. And hence I added another check.
For #1
Code: Select all
StringToDate(DecimalToString(YearFromDate(CurrentDate())-1):"-12-01", "%yyyy-%mm-%dd")
Code: Select all
If MonthFromDate(CurrentDate()) = 1 then StringToDate(YearFromDate(CurrentDate())-1:"-12-01", "%yyyy-%mm-%dd") else if Len(MonthFromDate(CurrentDate())-1) = 1 then StringToDate(YearFromDate(CurrentDate()):"-0":MonthFromDate(CurrentDate()) - 1:"-01", "%yyyy-%mm-%dd") else StringToDate(YearFromDate(CurrentDate()):"-":MonthFromDate(CurrentDate()) - 1:"-01", "%yyyy-%mm-%dd")
Another problem was cause by MonthFromDate(). It was returning a single digit month where as two digit month (%mm) is required. And hence I added another check.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.