Jump to content
  • 0
Sign in to follow this  
kme

Finding The Difference In Text Timestamps

Question

[Please skip to the first comment that I posted]

 

On this DataPage:

http://b4.caspio.com/dp.asp?AppKey=b05320005057b918f4354d1992ba

 

I want the TimeDifference column to have the difference between the 'SupportRequiredYes' and 'SupportReadyYes' timestamps, in days and hours. What is the simplest way to do this?

 

This is what I have so far, but there has to be an easier way. The code is after the comment "//TIME DIFFERENCE". This has it so that when 'SupportReadyYes' radio button on the Details Page is clicked Yes, the time difference is calculated.  

<script>
document.getElementById('EditRecordSupportRequired0').onclick= function () {
if (document.getElementById('EditRecordSupportRequired0').value == 'Y'){
var currentdate = new Date();
var datetime = (currentdate.getMonth()+1) + "/"
                + currentdate.getDate() + "/"
                + currentdate.getFullYear() + " "  
                + currentdate.getHours() + ":"  
                + currentdate.getMinutes() + ":"
                + currentdate.getSeconds();
document.getElementById('EditRecordSupportRequiredYes').value = datetime;
}
}

document.getElementById('EditRecordSupportReady0').onclick= function () {
if (document.getElementById('EditRecordSupportReady0').value == 'Y'){
var currentdate = new Date();
var datetime = (currentdate.getMonth()+1) + "/"
                + currentdate.getDate() + "/"
                + currentdate.getFullYear() + " "  
                + currentdate.getHours() + ":"  
                + currentdate.getMinutes() + ":"
                + currentdate.getSeconds();
document.getElementById('EditRecordSupportReadyYes').value = datetime;

//TIME DIFFERENCE FUNCTION

var supportready = document.getElementById('EditRecordSupportReadyYes').value;
var supportready_date1 = supportready.split('/', 1).join('/').length;
//finding the position of the first '/' character
var supportready_date2 = supportready.split('/', 2).join('/').length;
var supportready_date = supportready.substring((supportready_date1)+1, (supportready_date2));
//finding the date value, between the first two '/' characters
var supportrequired = document.getElementById('EditRecordSupportRequiredYes').value;
var supportrequired_date1 = supportrequired.split('/', 1).join('/').length;
var supportrequired_date2 = supportrequired.split('/', 2).join('/').length;
var supportrequired_date = supportrequired.substring((supportrequired_date1)+1, (supportrequired_date2));
if (supportrequired_date>supportready_date){
var datediff = (31-supportrequired_date)+parseInt(supportready_date);
//the '31' still needs to be tweaked according to each month
}
else{
var datediff =  supportready_date - supportrequired_date;
}

var supportready_time1 = supportready.split(' ', 1).join(' ').length;
var supportready_time2 = supportready.split(':', 1).join(':').length;
var supportready_time = supportready.substring((supportready_time1)+1, (supportready_time2));
var supportrequired_time1 = supportrequired.split(' ', 1).join(' ').length;
var supportrequired_time2 = supportrequired.split(':', 1).join(':').length;
var supportrequired_time = supportrequired.substring((supportrequired_time1)+1, (supportrequired_time2));
if (supportrequired_time>supportready_time){
var timediff = (24-supportrequired_time)+parseInt(supportready_time);
var datediff = datediff-1;
}
else{
var timediff = supportready_time - supportrequired_time;
}

document.getElementById('EditRecordTimeDifference').value = datediff + " days " + timediff + " hours";
}
}
</script>

I can keep what I have so far, except it gets so tedious when I have to consider a case like finding the difference between "3/29" and "4/4", where I have to see which month has 30 days or 31 days, etc. It's a pain.

 

I don't think the built-in Datediff function would work because the timestamp was formatted manually with JavaScript, as a text.

 

Any thoughts?

Share this post


Link to post
Share on other sites

1 answer to this question

Recommended Posts

  • 0

For those interested in the solution:

CAST(Datediff(hour, [@field:SupportRequiredYes], [@field:SupportReadyYes])/24 AS CHAR(15)) + 'days ' + CAST(Datediff(hour,[@field:SupportRequiredYes], [@field:SupportReadyYes])%24 AS CHAR(15)) + 'hours'

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...