You are not logged in.
Announcement
Unanswered posts
|
Pages: 1
- Index
- » Talend Open Studio for Data Integration » Usage, Operation
- » datediff between ms sql and java
#1 2012-03-29 10:56:24
- Joe
- Member
- Registered: 2011-01-07
- Posts: 102
datediff between ms sql and java
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
Offline
#3 2012-03-29 17:52:51
- paula11
- Member
- Registered: 2011-05-27
- Posts: 11
Re: datediff between ms sql and java
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).
Offline
#4 2012-03-30 07:50:27
- Joe
- Member
- Registered: 2011-01-07
- Posts: 102
Re: datediff between ms sql and java
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
Offline
#5 2012-03-30 10:03:29
- paula11
- Member
- Registered: 2011-05-27
- Posts: 11
Re: datediff between ms sql and java
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
Offline
#6 2012-03-30 10:42:52
- kloperto
- Guest
Re: datediff between ms sql and java
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.
#8 2012-04-01 09:34:25
- Joe
- Member
- Registered: 2011-01-07
- Posts: 102
Re: datediff between ms sql and java
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.
Offline
Pages: 1
- Index
- » Talend Open Studio for Data Integration » Usage, Operation
- » datediff between ms sql and java