Substraction of month from the date

siva4joy · Post by **siva4joy** » Mon Apr 21, 2008 12:23 pm

hi,

could you please help me to find is there any function to substarct months from a given date.

i need to filter the records from the source data on a date column , if the 1st day of the previous month's date is friday then add 7 days to the particular date, filter those records which are not greater than the resultant date.

eg: 01-02-2008 is friday and i need to filter the records which are not greater than 08-02-2008. but my jobs are running in march.

Appreciate your help on this.

kumar_s · Post by **kumar_s** » Mon Apr 21, 2008 12:53 pm

This is one of the recent discussion about adding month, manipulate it.

viewtopic.php?t=118119&highlight=month

You can search for more different methodologies. But your post seems to be more focusing on adding 7 days of data to it??

savis · Post by **savis** » Mon Apr 21, 2008 1:10 pm

Hi,

Did you use this function:

DateFromDaysSince(7,StringToDate(<Date Field>,"%dd-%mm-%yyyy") )

Input output
01-01-2008 2008-01-08
11-02-2008 2008-02-18
01-03-2008 2008-03-08
21-02-2008 2008-02-28
01-02-2008 2008-02-08
05-01-2008 2008-01-12
03-02-2008 2008-02-10
05-02-2008 2008-02-12

Thanks,
Savis

siva4joy · Post by **siva4joy** » Mon Apr 21, 2008 3:39 pm

the logic should be ,whenever the first of the previous month falls on friday only i need to filter the records.
but how can i find the first of the previous month is friday?
for example if my job runs on today 21-04-08 then i need to check the first of the previous month 01-03-08 is friday or not .

appreciate your help to find out.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 21, 2008 5:11 pm

Do you have a time dimension table you can use in a lookup?

siva4joy · Post by **siva4joy** » Tue Apr 22, 2008 3:40 am

no we don't have any dimension tables.
the source data is coming from the flat file and we need to load in oracle.

ray.wurlod · Post by **ray.wurlod** » Tue Apr 22, 2008 3:48 am

Create a "time" table with daily granularity. You'll appreciate having it.

anbu · Post by **anbu** » Tue Apr 22, 2008 8:48 am

Code: Select all

date +%Y" "%m | perl -ane ' use Time::Local; $wday = (localtime(timelocal(0,0,0,1,$F[1]-2,$F[0]-1900)))[6]; print $wday; '

Above code prints number ranges from 0 (for Sunday) through 6 (for Saturday). If it returns 5 then 1st day of previous month started on friday.

siva4joy · Post by **siva4joy** » Wed Apr 23, 2008 3:17 am

I think a solution for this.
Take the year and month from the CurrentDate().
The date required is yyyy : (mm-1)only the value range between 02 and 12 : 01 . Use StringToDate function.
Then use the function WeekdayFromDate(1st of thePrevious Month Date) if it returns 5 then it is friday which i am looking for.

But it needs to test for my requirement.