difference of dates
Moderators: chulett, rschirm, roy
difference of dates
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]
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
ssh023
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
Amsterdam
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
[url]viewtopic.php?t=107280&highlight=date+difference[/url]
Thanks
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
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
ssh023
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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?
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
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?
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