Ewan Whyte · Jun 22, 2020

Amending timestamp

Hi, I have a timestamp of 201906192359 with a HL7 and I need to add a minute to it to get 201906200000. Is there an easy way within Healthshare to do this?

It seems easy enough within SQL but I cannot get the SQL to work within Healthshare,  this is what I have for SQL which does the job in SQL Server.

 SELECT REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),DATEADD(mi,1,STUFF(STUFF(@test,11,0,':'),9,0,' ')),120),'-',''),' ',''),':','');


Within Healthshare I've tried to use this SQL to populate a :dateout variable using this code but it isn't updating :dateout. (datein: = 201906192359)

 &sql(SELECT REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),DATEADD(mi,1,STUFF(STUFF(:datein,11,0,':'),9,0,' ')),120),'-',''),' ',''),':','')
 INTO :dateout)

Thanks for your help.

0 3 87 3


embedded SQL is probably an overkill for this formating.
Just convert it to the internal format, add what you need and assemble the pieces in COS

; set datein=201906192359
; set add=60    ;; seconds to add
set dh=$ZDH($e(datein,1,8),8)
set hh=$e(datein,9,10)
set mi=$e(datein,11,12)
set new=$ZTH(hh_":"_mi_":00",3)+add
set zdt=$zdt(new\86400+dh_","_(new#86400),3)
set dateout=$tr($e(zdt,1,*-3),"-: ")

Hi Ewan,

Slightly I have modified you query, its working fine.

&sql(SELECT REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),DATEADD(mi,1,STUFF(STUFF(STUFF(STUFF(201906192359,11,0,':'),9,0,' '),5,0,'-'),8,0,'-')),120),'-',''),' ',''),':','') into :OutVal)

There's also a convenience function for this:

set origTime=$h
set newTime=$$HorologAddSecs^EnsUtil(origTime,60)

The first parameter is the time in $horolog format, the second parameter is the number of seconds to add. It returns the new time in $horolog format.