Page 1 of 2
Date conversation
Posted: Mon Mar 12, 2007 9:36 am
by srini.dw
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,
Posted: Mon Mar 12, 2007 9:41 am
by DSguru2B
Have you tried doing it yourself? What have you tried so far? It can be done easily using out of the box functions.
Posted: Mon Mar 12, 2007 9:43 am
by kcbland
Where do you need this? In a stage variable? In a query? In a filename?
Posted: Mon Mar 12, 2007 9:46 am
by DSguru2B
Another question, You query just includes current date. Whats the purpose of the parameter (2007-01-01) that you mentioned in your query?
Posted: Mon Mar 12, 2007 1:03 pm
by trobinson
I thought this was a post on how to score with chicks.
Posted: Mon Mar 12, 2007 1:04 pm
by DSguru2B
....more like how do keep chicks interested.
Posted: Mon Mar 12, 2007 1:15 pm
by DSguru2B
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:
Code: Select all
StringToDate(DecimalToString(YearFromDate(CurrentDate())-1):"-12-01", "%dd-%MMM-%yy")
and for #2:
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")
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.
Posted: Mon Mar 12, 2007 7:01 pm
by ray.wurlod
I thought it was talking about those fruits that originate in the Middle East.
Posted: Mon Mar 12, 2007 9:48 pm
by kumar_s
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;
Posted: Mon Mar 12, 2007 9:49 pm
by kumar_s
trobinson wrote:I thought this was a post on how to score with chicks.
Our site is not, that helpful.
Re: Date conversation
Posted: Tue Mar 13, 2007 6:46 am
by DSguru2B
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;
This query will break every time its run in January.
Posted: Tue Mar 13, 2007 6:56 am
by kumar_s
As suggested by DSguru2B, check for month 01 or 12, and subtract the year by 1. You can either use Decode() or Case() in SQL or If then Else in Tranformer.
Posted: Tue Mar 13, 2007 9:26 am
by srini.dw
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
Posted: Tue Mar 13, 2007 10:24 am
by DSguru2B
The format was messed up. Use the following:
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")
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.
Posted: Wed Mar 14, 2007 12:44 am
by srini.dw
Hi,
Thanks for the code, 2nd one works fine, but 1st one still giving **** in the output, (datatype date)
Thanks,