Clarion Date Conversion

Question

Clarion Date Conversion

jason_in_mi

SSC Veteran

Points: 294
More actions
October 7, 2015 at 5:11 pm

#321178

The database for our software stores dates and times in clarion format. I'm trying to write some custom reports in T-SQL and I need to convert these dates but it's giving me a lot of trouble. How do I query the dates and times and have the results shown as a "regular" date/time? Below is what I have so far which is really the very beginnings of this report. Basically on the results/output I need the ClarionDate and ClarionTime to be shown as typical date/time columns. I did some research on my own but I'm having a hard time grasping this. I believe they have to be pulled into a temp table and then converted? If anyone can explain this I'm sure I'll be able to grasp it once I see the correct code or point me in the direction where I can hopefully figure it out on my own.
Thank you in advance!!!
SELECT A1.DATEOFACCESS ClarionDate, A1.TIMEOFACCESS ClarionTime, A2.NAME Event
FROM dbo.History A1
JOIN dbo.SysEvents A2
ON A1.RSVD_Action = A2.RSVD_EVENTTYPE

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

jason_in_mi SSC Veteran Points: 294 More actions · Answer 1

OK So doing some more research people recommended creating a view to convert the the clarion date/times. The problem is I can only seem to convert the DATE column to a datetime field so it should the correct date but a time of 00:00:00.

I found someone who had some simple commands to convert these two clarion columns to one datetime column. Would this work to create this in a view? Any suggestions?

Link - Clarion to datetime?

T-SQL that returns the clarion date as a datetime @00:00:00

CREATE VIEW Actual_Date_Time AS

SELECT CAST(DATEOFACCESS - 36163 AS datetime) AS DATEOFACCESS,TIMEOFACCESS,RECORDUNIQUEID

FROM dbo.History

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 2

Perhaps something like this?

CREATE TABLE #SomeClarionValues (DATEOFACCESS int, TIMEOFACCESS int)

INSERT INTO #SomeClarionValues

VALUES (78439,1),(78439,69087),(78439,40001), (75247,7511378),(75247,8630000)

SELECT

ClarionConvertedToDatetime=DATEADD(MILLISECOND,(TIMEOFACCESS-1)*10,DATEADD(DAY,DATEOFACCESS,'1800-12-28T00:00:00.000'))

FROM #SomeClarionValues

DROP TABLE #SomeClarionValues

Cheers!

EDIT: I had mistyped and put a couple identical rows in the temp table. Not a big deal, but was a bit silly, so I changed the duplicate row. I also changed some of the times to show a greater range there.