difference of dates

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
ssh023
Participant
Posts: 18
Joined: Tue Nov 21, 2006 9:14 pm

difference of dates

Post by ssh023 »

Hi All,

I have two dates and I need to get a difference of those two dates and divide it by 7 to get the number of weeks. The two dates are in mm/dd/yyyy format.

I was going through the posts on the same topic and found this:

(Iconv(date1,"D/DMY") - Iconv(date2,"D/DMY"))/7

But when I use this, it is insering 0(not the actual difference). The result column is of the datatype number(33,5).
Please help me resolve this! [/code]
Regards,
ssh023
WoMaWil
Participant
Posts: 482
Joined: Thu Mar 13, 2003 7:17 am
Location: Amsterdam

Post by WoMaWil »

You have to read the help-file on how to use iconv and oconv. The first "D" means date, the second day and so on. If you use first the right string-operations before converting it will run. Check it you have correct dates, 30th February does not exist for example. Check for null-values. And than it will calculate as you like.
Wolfgang Hürter
Amsterdam
ssh023
Participant
Posts: 18
Joined: Tue Nov 21, 2006 9:14 pm

Post by ssh023 »

Hey WoMaWil,

Thanks for the quick reply, I checked the dates....there is no 30th February and I do not have any null values for the dates. It is still inserting the zeros, please let me know if there is any other function I can use besides Iconv
Regards,
ssh023
NBALA
Participant
Posts: 48
Joined: Tue Jul 11, 2006 11:52 am
Location: IL, USA

Post by NBALA »

Hello ! Below post may help you..it will return a number then you can divide by 7

[url]viewtopic.php?t=107280&highlight=date+difference[/url]

Thanks
ssh023
Participant
Posts: 18
Joined: Tue Nov 21, 2006 9:14 pm

Post by ssh023 »

It is still inserting the zeros when I use this:

ABS(ICONV('20-09-2006','D4DMY')-ICONV('23-03-1983','D4DMY'))

The date format for the two dates is mm/dd/yyyy 00:00:00.

Is it inserting the zeros because of the time stamp (00:00:00)? If yes, please let me know how i can eliminate the timestamp and then get the difference
Regards,
ssh023
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Are your dates in day-month-year order? Is "/" really the delimiter?

Give us some example data.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Ronetlds
Participant
Posts: 28
Joined: Thu Mar 30, 2006 12:48 pm

Post by Ronetlds »

(Iconv(date1,"D/DMY[2,2,4]")
Ronetlds
Participant
Posts: 28
Joined: Thu Mar 30, 2006 12:48 pm

Post by Ronetlds »

Sorry,
convert dates as

Iconv(daten,"D/MDY[2,2,4]")
Ronetlds
Participant
Posts: 28
Joined: Thu Mar 30, 2006 12:48 pm

Post by Ronetlds »

Sorry for the multiples. If your date format is

mm/dd/yyyy 00:00:00

then yes, the timestamp will kill the Iconv.

To fix, pass the date as a char, then remove timestamp (Field function - keeps first field before space in this example - look at Help) and apply Iconv:

Iconv(Field(DateandTS," ",1),"D/MDY[2,2,4]")

where DateandTS is one of your input columns.
DeepakCorning
Premium Member
Premium Member
Posts: 503
Joined: Wed Jun 29, 2005 8:14 am

Post by DeepakCorning »

It will be easier for us to give u a solution if you can give us an example of the date1 field.
Thanks
Deepak Patil

Convince Them Confuse Them .. What's the difference?
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Just take the leftmost 10 characters of each "timestamp".

Code: Select all

(Iconv(Left(TS1,10),"DDMY"-Iconv(Left(TS2,10),"DDMY")/7
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
ssh023
Participant
Posts: 18
Joined: Tue Nov 21, 2006 9:14 pm

Post by ssh023 »

Hi All,

Thanks a lot for your replies!

I am sorry I had given the wrong format for the date.

The actual date format is 'YYYY-MM-DD 00:00:00', the example of the date is '2007-12-31 00:00:00', both the dates have the same format. Does any one of the codes mentioned work for this date format?
Regards,
ssh023
ssh023
Participant
Posts: 18
Joined: Tue Nov 21, 2006 9:14 pm

Post by ssh023 »

Hi All,

Thanks a lot for your replies!

I am sorry I had given the wrong format for the date.

The actual date format is 'YYYY-MM-DD 00:00:00', the example of the date is '2007-12-31 00:00:00', both the dates have the same format. Does any one of the codes mentioned work for this date format?
Regards,
ssh023
Ronetlds
Participant
Posts: 28
Joined: Thu Mar 30, 2006 12:48 pm

Post by Ronetlds »

Iconv(Field(DateandTS," ",1),"D-YMD[4,2,2]")
ssh023
Participant
Posts: 18
Joined: Tue Nov 21, 2006 9:14 pm

Post by ssh023 »

Awesome! Thanks Ronetlds, for the quick turn around!

The code "Iconv(Field(DateandTS," ",1),"D-YMD[4,2,2]")" is working perfectly fine , it was showing the negative value for some of the weeks calculated but i got rid of those using the Abs().
Regards,
ssh023
Post Reply