Solved

FSM Mobile - compare two dates with getdbvalue

  • 24 January 2023
  • 5 replies
  • 156 views
D
Rank
Userlevel 2
Badge +9

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);

 

 

icon

Best answer by nimesha.kalinga 24 January 2023, 14:20

View original

5 replies

N
Rank
Badge +4

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

 

D
Rank
Userlevel 2
Badge +9

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.”

N
Rank
Badge +4

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

 

 

D
Rank
Userlevel 2
Badge +9

@nimesha.kalinga thanks!

A
Rank
Badge +2

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

Reply


Terms & ConditionsCookie settings