Written by

Question Nezla · Sep 14, 2022

Comparing %TimeStamp data types

#Ensemble

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

Add reply

Comments

David Hockenbroch · Sep 14, 2022

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.

0 0

Nezla Sep 15, 2022 to David Hockenbroch

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

0 0

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

0 0

Nezla Sep 15, 2022 to Cristiano Silva

Thanks Cristiano

0 0

Julius Kavay Sep 15, 2022 to Nezla

what you need is a SIMPLE compare of two strings

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

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

0 0