Clarion Date Conversion

  • 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

  • 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

  • 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.

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

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