New post
Question
· Jun 22, 2020

Amending timestamp

#HL7 #SQL #Ensemble

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.

Discussion (3)1
Log in or sign up to continue
Robert Cemper · Jun 22, 2020

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),"-: ")
2 0