Convert Clarion Standard Time to SQL Time or SQL Time to Clarion Time

code Snippets
1

If you have an INT data type in your SQL database that hold a Clarion Time value you can convert it to SQL Time using this snippet:
select convert(time, dateadd(ms, (ClarionTimeField-1)*10,0))
or to get a text format time string
select convert(varchar, dateadd(ms, (ClarionTimeField-1)*10,0),114)

DateAdd with ms means milliseconds. Clarion time is the number of hundreds of seconds since midnight + 1. Multiplying by 10 gets you to milliseconds and then you can convert to the data type you want.

HTH,
Rick

2 Likes
2

Thanks Rick!
Just noting this topic as related:

3

Just for completeness. Here is the T-SQL to convert a SQL DateTime to a Clarion time.

    DECLARE @ClarionTime INT
           ,@SQLDateTime DATETIME
           ,@Hours       INT
           ,@Minutes     INT
           ,@Seconds     INT
           ,@Hundredths  INT;
SET @SQLDateTime = GETDATE();
SELECT @Hours = DATEPART(HOUR, @SQLDateTime), @Minutes = DATEPART(MINUTE, @SQLDateTime), @Seconds = DATEPART(SECOND,@SQLDateTime), @Hundredths = DATEPART(MILLISECOND,@SQLDateTime)/10
SET @ClarionTime = (@Hours * 360000) + (@Minutes * 6000) + (@Seconds * 100) + @Hundredths + 1
SELECT @SQLDateTime, @ClarionTime
3 Likes