DSXchange

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]

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.

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

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

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

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

Give us some example data.

(Iconv(date1,"D/DMY[2,2,4]")

Sorry,
convert dates as

Iconv(daten,"D/MDY[2,2,4]")

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.

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

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

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?

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?

Iconv(Field(DateandTS," ",1),"D-YMD[4,2,2]")

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().