Date conversation

[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,

[DSguru2B]

Have you tried doing it yourself? What have you tried so far? It can be done easily using out of the box functions.

[kcbland]

Where do you need this? In a stage variable? In a query? In a filename?

[DSguru2B]

Another question, You query just includes current date. Whats the purpose of the parameter (2007-01-01) that you mentioned in your query?

[trobinson]

I thought this was a post on how to score with chicks.

[DSguru2B]

....more like how do keep chicks interested.

[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.

[ray.wurlod]

I thought it was talking about those fruits that originate in the Middle East.

[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; 

[kumar_s]

I thought this was a post on how to score with chicks.

Our site is not, that helpful.

[DSguru2B]

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.

[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.

[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

[DSguru2B]

The format was messed up. Use the following:
For #1

Code: Select all

StringToDate(DecimalToString(YearFromDate(CurrentDate())-1):"-12-01", "%yyyy-%mm-%dd") 

For #2

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.

[srini.dw]

Hi,

Thanks for the code, 2nd one works fine, but 1st one still giving **** in the output, (datatype date)

Thanks,