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 165
Discussion (3)3
Log in or sign up to continue

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.