Convert a Clarion Date (INT) to SQL DateTime

clariondate.sql

/*
A Clarion standard date is the number of days that have elapsed since 
December 28, 1800. The range of accessible dates is from January 1, 1801 
(standard date 4) to December 31, 9999 (standard date 2,994,626). Date 
procedures will not return correct values outside the limits of this range. 
The standard date calendar also adjusts for each leap year within the range of 
accessible dates. Dividing a standard date by modulo 7 gives you the day of the 
week: zero = Sunday, one = Monday, etc.

If you are working with the newer DATE type fields (introduced in MSSQL2008) 
then you need to make some adjustments to the techniques shown above. You 
would need to use the SQL DateDiff() function to convert those DATE fields 
to 4 byte integers, so that they can be directly compared to the (adjusted by -36163)
numeric CW Date integer.
*/

DECLARE @ClarionToUnixDiff INT = 36163
DECLARE @UnixToClarionDiff INT = 4
DECLARE @ClarionDateBase DATE = '1801-01-01'

-- examples
DECLARE @unixDate DATE = '2015-11-09'
DECLARE @clarionDate INT = 78478

SELECT
    CONVERT(datetime,@clarionDate-@ClarionToUnixDiff) AS UnixDateTime, -- Clarion date to ISO datetime
    CONVERT(date,CONVERT(datetime,@clarionDate-@ClarionToUnixDiff)) AS UnixDate, -- Clarion date to ISO date
    DATEDIFF(d, @ClarionDateBase, @unixDate)+@UnixToClarionDiff as ClarionDate -- ISO date to Clarion date
;