Convert Clarion Date to SQL DateTime

brahn · May 28, 2014, 2:45pm

If you have stored your dates in clarion format this snippet can be used to convert them to SQL DateTime values.

gist.github.com

https://gist.github.com/fushnisoft/5857270

clariondate.sql

DECLARE @ClarionDate INT = 47563
DECLARE @SqlDateTime DATETIME 

-- Convert the clarion DATE into and SQL DateTime
SET @SqlDateTime = DateAdd(day, @ClarionDate  - 4, '1801-01-01') 

SELECT @SqlDateTime AS 'SQL Date Time'

-- Now convert it back from and SQL DateTime to a Clarion Date
SET @ClarionDate = DateDiff(day, DateAdd(day, -4, '1801-01-01'), @SqlDateTime)

This file has been truncated. show original

brahn · May 31, 2014, 6:25pm

I think there may be an issue with how this handles nulls and out of range dates though.

brahn · May 31, 2014, 6:26pm

This could be an interesting project to watch too:

Mike_Duglas · October 8, 2018, 5:17pm

For MySql this works:

DROP FUNCTION IF EXISTS get_sql_date;
CREATE FUNCTION get_sql_date(clarionDate INT)
  RETURNS DATE 
  
  DETERMINISTIC
BEGIN

  DECLARE sqlDate DATE;
  SET sqlDate = DATE_ADD('1801-01-01', INTERVAL clarionDate  - 4 DAY); 
  
  RETURN sqlDate;
  
END