Convert Date/Time to HL7 Date/Time

[thorntong]

Hi,

I'm working with an SQL 2000 DB to extract an appoitment date/time and trying to format it so that I can insert it into an HL7 message. Here is the command that I am using to extract the date and time fields:

CONVERT(varchar(20),dateadd(day,appt.apptdate-1,'12/31/1899'),102)+Convert(datetime,tsr.displayvalue) as apptDtTm

My results using the above code is: 2009-04-23 16:00:00.000

The format that I am trying to achieve is:

200904231600 (CCYYMMDDHHSS)

Is there an way in which this can be done using SQL? I have been successful to get just the date field to work using the following code:

CONVERT(varchar(20),dateadd(day,appt.apptdate-1,'12/31/1899'),112) as apptDT

Which give me this as a result: 20090423

So I'm stuck as to get the 'Time' section. I would greatly appreciate any/all assistance that you can provide.

Kind Regards,
Glenn

 

[gmmastros]

CCYYMMDDHHSS

The last 2 (SS).... should this really be minutes (not seconds)?

If it's minutes you want, I would suggest something like this...

Select Replace(Replace(Replace(Convert(VarChar(16), GetDate(), 121), '-', ''), ' ', ''), ':', '')

** replace GetDate() with your column. GetDate() is there just to get the formatting right.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[thorntong]

Hi George,

You were right about the Minutes, and not the seconds. But as you can see I'm combining a date conversion with a time conversion and that's okay and the code you provided me works great but only when you use the GetDate() as a date. I can split the date and time into separate fields and get the date field in the format I need, but for some reason CONVERT and REPLACE commands when focusing only on the Time section doesn't always play nice with one another. I need to have the date in a military format using the 24-hour clock.

Any ideas?

Thank you,

Glenn

 

[gmmastros]

Are your Date and time values coming from separate columns within the tables(s). If so, can you post a couple rows worth of data and the expected results?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[thorntong]

Hi George,

Sure. My time DB table looks like this:

LookUpType    TID    DisplayValue
1             135    5:10 PM
1             136    5:15 PM
1             137    5:20 PM
1             138    5:25 PM
1             129    5:30 PM

My date DB table looks like this:

VID      apptdate      drid
123      39926         1504
234      39922         1420
345      39926         1422
456      39918         1347

But here is that acutal query that I am using:

SELECT 
       '1' AS SETID,
       'HP' AS DOCTYPE,
       'TX' AS DOCCONTENT,
       CONVERT(varchar(16),dateadd(day,appt.apptdate-1,'12/31/1899'),112)+Convert(datetime,tsr.displayvalue,112) as apptDTTM ,
       prov.drid,
       prov.lastname,
       prov.firstname,
       prov.middle,
       prov.suffix,
       prov.prefix,
       vst.vid
FROM Appointments appt WITH (NOLOCK) INNER JOIN Visits vst WITH (NOLOCK) ON appt.appid = vst.appid 
     INNER JOIN TSReportLookup tsr WITH (NOLOCK) ON appt.starttime=tsr.tid
     LEFT OUTER JOIN Providers prov ON appt.entityid=prov.drid
WHERE vst.VisitCreatedOn >= DateAdd(day, DateDiff(Day, 0, GetDate()-5), 0) AND 
      vst.VisitCreatedOn < DateAdd(day, DateDiff(Day, 0, GetDate()), 0)

Kind Regards,
Glenn

 

[gmmastros]

Try this:

Replace(Replace(Replace(Convert(VarChar(16), dateadd(day,appt.apptdate-1,'12/31/1899') + Convert(datetime,tsr.displayvalue), 121), '-', ''), ' ', ''), ':', '')

I know it's ugly, but I think the parenthesis are in the right place. This would probably be a good candidate for a user defined function.

Create Function dbo.FormatHL7(
     @DayNumber Int,
     @TimeDisplay VarChar(8))
Returns VarChar(20)
As
Begin
	Return Replace(Replace(Replace(Convert(VarChar(16), dateadd(day,@DayNumber-1,'12/31/1899') + Convert(datetime,@TimeDisplay), 121), '-', ''), ' ', ''), ':', '')
End

Create the above function (just once). Then you can use it from anywhere in your code like this...

SELECT
       '1' AS SETID,
       'HP' AS DOCTYPE,
       'TX' AS DOCCONTENT,
       [!]dbo.FormatHL7(appt.apptdate,tsr.displayvalue) as apptDTTM ,[/!]
       prov.drid,
       prov.lastname,
       prov.firstname,
       prov.middle,
       prov.suffix,
       prov.prefix,
       vst.vid
FROM Appointments appt WITH (NOLOCK) INNER JOIN Visits vst WITH (NOLOCK) ON appt.appid = vst.appid
     INNER JOIN TSReportLookup tsr WITH (NOLOCK) ON appt.starttime=tsr.tid
     LEFT OUTER JOIN Providers prov ON appt.entityid=prov.drid
WHERE vst.VisitCreatedOn >= DateAdd(day, DateDiff(Day, 0, GetDate()-5), 0) AND
      vst.VisitCreatedOn < DateAdd(day, DateDiff(Day, 0, GetDate()), 0)

Generally, I dislike functions because developers tend to over-use them. In this case, it's a scalar-valued function that is really just hiding some implementation details (without going to any tables).

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom