Oracle Date logic

kumar444 · Post by **kumar444** » Fri Jan 28, 2011 4:50 pm

Can anybody please tell me how to implement this date logic in datastage?

Oracle :
trunc(last_day(add_months(sysdate,-1)))

chulett · Post by **chulett** » Fri Jan 28, 2011 5:24 pm

In order to get help from the largest pool of people, you really should restate the question in words - meaning, explain what the Oracle sql is doing. Or is that part of the issue?

kumar444 · Post by **kumar444** » Fri Jan 28, 2011 8:48 pm

Thanks Craig I will keep this in mind while posting hereafter. Any suggestions on how to make this logic work in datastage.

jwiles · Post by **jwiles** » Fri Jan 28, 2011 10:00 pm

Do you know what the sql logic does? If so, please describe it in the thread. Once the desired results are known, then a solution can quickly be found.

If you don't know what the sql does, please say so. Then someone can describe that and you will learn that much more!

Regards,

kumar444 · Post by **kumar444** » Fri Jan 28, 2011 10:09 pm

Sorry for not being precise.
This code will get the values or records of midnight of last day of last month.

For example:If sysdate is 12-Dec-2011 then the result is 30-Nov-2011 12:00am or 1-Dec-2011 00:00 am.

ray.wurlod · Post by **ray.wurlod** » Fri Jan 28, 2011 10:35 pm

Well, the time part is easy, as that's a constant.

As to the date part, simply subtract one more than the day number from the date, using DateFromDaysSince() function.

Code: Select all

DateFromDaysSince(svTheDate, DayFromDate(svTheDate) + 1)

kumar444 · Post by **kumar444** » Sat Jan 29, 2011 3:49 pm

Thanks Ray but i could only see the first line of your content.

ray.wurlod · Post by **ray.wurlod** » Sat Jan 29, 2011 10:00 pm

What you need to get is a premium membership - this will allow you to read the entirety of the premium posters' contributions, and will help to fund the continued existence of DSXchange. All premium membership fees are devoted to the hosting and bandwidth costs of DSXchange.

kumar444 · Post by **kumar444** » Sat Jan 29, 2011 11:11 pm

Ray I would like to do that but need some time.