Comparing %TimeStamp data types

Question

Question

Nezla · Sep 14, 2022

Hi Guys,

I do have a class where one it's fields is defined as %TimeStamp and I did query to select all records and if current datetime is bigger then what's in my nextScheduled filed and basically with this condition below I should have all records stored in ^badis but for some reason I'm only getting the first record

S sql="Select ID,CollectTimeOut,KitId,ComponentId,Loc,IntervalValue,IntervalType,LastScheduled,NextScheduled,Schedule,StartDate,StartTime,SensorId,SensorType from MSDS_Common.JobSchedule"
Set RSet=##class(%ResultSet).%New()
Set Ret=RSet.Prepare(sql)
Set Ret=RSet.Execute()
While RSet.Next()
{ Set routeGuid=""
Set nextScheduled=RSet.GetData(9)
I $ZDATETIME($h,3,1)>nextScheduled S ^badis("datetime",Id)=$ZDATETIME($h,3,1)_"|"_nextScheduled
}

here are the records from my class

and here I should be getting all 12 records,

Thanks

Product version: Ensemble 2014.1

Discussion (5)2

Log in or sign up to continue

Cristiano Silva · Sep 14, 2022

Hi @Nezla

Complementing @David Hockenbroch answer, you need to change the If statement, because we can't compare TimeStamp directly, even in the internal format, because in the comparision they are treated like strings.

Use some one of these methods:

$System.SQL.Functions.DATEDIFF

##Class(%Library.UTC).Compare

##Class(%Library.UTC)Diff

1 0

score 0 · Answer 1 · 2022-09-14T08:13:19-04:00

Rochdi, where is the variable Id getting its value? You're setting ^badis("datetime",Id) but I don't see anywhere in your loop where Id gets updated, so maybe you're just overwriting the same value, ^badis("datetime",1), over and over.

score 0 · Answer 2 · 2022-09-15T04:12:07-04:00

sorry missed to copy Set Id=RSet.GetData(1), but it's my original code, and it's not the problem in this case

score 0 · Answer 3 · 2022-09-15T04:12:29-04:00

Nezla · Sep 15, 2022

Thanks Cristiano

0 0

score 0 · Answer 4 · 2022-09-15T09:40:53-04:00

what you need is a SIMPLE compare of two strings

Set Ret=RSet.Execute()
set currentTS = $zdt($h,3)    // get onece the current timestamp
 While RSet.Next()
  { Set routeGuid="" 
Set nextScheduled=RSet.GetData(9)
//I $ZDATETIME($h,3,1)>nextScheduled S ^badis("datetime",Id)=$ZDATETIME($h,3,1)_"|"_nextScheduled
if currentTS ] nextScheduled S ^badis("datetime",Id)=currentTS_"|"_nextScheduled
}

In words: if currentTS follows (i.e. greater) nextScheduled - that's all.