Skip to main content

Hi all,

I want to compare two datetimes, and get the amount of seconds left.

I tried 

SELECT DATEDIFF(second, GETDATE(),ACTUAL_TRAVEL_START_DTTM) FROM task WHERE task_id = {0}

 

This one works via the sql query tool. But if I use it in a getdvalue, no value is returned 

 

getDBValue("select CONVERT(TIME,ACTUAL_TRAVEL_START_DTTM - GETDATE()) from task where task_id = {0}", taskid);

 

 

Hi @drooij,

 

Assuming the question is about FSM Mobile, it uses SQLLite database which uses somewhat different database functions.

 

Can you try using the following example. Which calculates difference of seconds between two dates (date vs current date)

 

select task_id, created_dttm,
Cast ((
    JulianDay(date('now')) - JulianDay(created_dttm)
) * 24 * 60 * 60 As Integer) as resultInSeconds
from task

 

Hi @nimesha.kalinga ,

Thanks for the reply. Is there something missing in your query? Because JulianDay is not definend? 

“'JulianDay' is not a recognized built-in function name.”

Hi @nimesha.kalinga ,

Thanks for the reply. Is there something missing in your query? Because JulianDay is not definend? 

“'JulianDay' is not a recognized built-in function name.”

Hi @drooij , 

can I know where you have tried executing the above function? Is it on the Mobile database?

Attaching the results I’m getting when I executed in the mobile database.

 

Query:

select task_id, created_dttm, Cast ((JulianDay(date('now')) - JulianDay(created_dttm)) * 24 * 60 * 60 As Integer) as resultInSeconds from task

 

 

@nimesha.kalinga thanks!

@drooij Could you Pls let me know what was the outcome after applying above proposed solution?

Reply


Terms & ConditionsCookie settings