Post a reply

Write your message and submit

Options

Click in the dark area of the image to send your post.

Go back

Topic review (newest first)

Joe
2012-04-01 09:34:25

i tried a new method.

write a user-define function named dateDiff

public static int dateDiff(Date issueDate,Date originalInvoiceDate){
          
           
                                long day1 = issueDate.getTime()/1000/60/60/24;
                long day2 = originalInvoiceDate.getTime()/1000/60/60/24;
                int day = (int)(day1-day2);
                return day;
    }
if i input two datetime vars manually,it tests right;but if i input the two datetime through database,the result is wrong.

Joe
2012-04-01 02:27:51

Hi paula11
I tried this both on TOS 4.1.2 and TOS 5.0.2 but i get a result of 2.

kloperto
2012-03-30 10:42:52

TalendDate just divides the difference in miliseconds by (1000*60*60*24), so you it only gives one day difference if the difference is between one to two days. If the difference is less than 86400000 miliseconds you get zero days difference.

paula11
2012-03-30 10:03:29

Hi Joe,

if it is not too much work to you, try a little new test-job (you will need 2 context-Variables IssueDate and OriginalInvoiceDate, both type Date). - See also screenshots.

If the result of this test-job still 2, I think there is a bug in your TOS. Otherwise there is a little failure in your original-job.

regards paula11

Joe
2012-03-30 07:50:27

Hi paula11

the schema is right the "yyyy-MM-dd",and i had tried to convert to datetime,but not work.
what's more,i did like this in eclipse,it work fine,and got right result.  but in tos ,it puzzles me.


Joe

paula11
2012-03-29 17:52:51

Without time in dateformat you should get back 3. Have a look at your schemas - this fields shoud have only dateformat "yyyy-MM-dd".

Alternatively you could select the databasefield as DATE (not VARCHAR) and set the dateformat in Talend-schema of database-Select as "yyyy-MM-dd" (time-Information then get lost / will ignore).

Joe
2012-03-29 11:01:09

e.g.

IssueDate = 2008-03-10 08:45:49.000
OriginalInvoiceDate = 2008-03-07 11:33:27.000

SELECT DATEDIFF(DAY,OriginalInvoiceDate,IssueDate) = 3
but the result from TOS is 2

Joe
2012-03-29 10:56:24

Hi all

as we know that,datediff function defined in TOS and in ms sql are not absolutely the  same.

e.g.
date A='2012-03-28 23:59:59.999'
date B='2012-03-29 00:00:00.000'

IN SQL:
SELECT DATEDIFF(DAY,B,A)
we get a result : 1
In TOS:
TalendDate.diffDate(A,B,'dd')
we get a result : 0

in java ,we calculate it as (B.getTime()-A.getTime())/1000/60/60/24
but in ms sql,it doesn't. day is the min measurement units.

so,to keep the result identical,i process in tos as following.
add another column with the same value for datediff.
     1.when i get the date from database,
                CONVERT(VARCHAR(10),IssueDate,120) as issueDate_diff
                CONVERT(VARCHAR(10),OriginalInvoiceDate,120) as   OriginalInvoiceDate_diff
     2.in tmap
                TalendDate.ParseDate("yyyy-MM-dd",issueDate_diff)
                TalendDate.ParseDate("yyyy-MM-dd",OriginalInvoiceDate_diff)
     3.datediff operation
                TalendDate.DateDiff(issueDate_diff,OriginalInvoiceDate_diff,'dd')

as this way,I thougt that it should be the same with MS SQL,however,we also get a one-day-difference result for some rows.

can anyone express it particularly for me ? in eclipse,i test it and be right,but in tos ,it is wrong .


Thanks very much for any suggestion.
BestRegards,
Joe

Board footer

Powered by FluxBB