Для получения полного доступа
зарегистрируйтесь.
Сниппет,  SQL

Конвертирование даты из Clarion (integer) в ISO 8601 (date/datetime)


Результат вынужденой работы с программами, написанными на Clarion. Конвертирование даты из формата Clarion (integer) в ISO 8601 (date/datetime)

-- MSSQL DB
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
;

/*
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.
*/
  sql MS SQL Server Clarion

Автор


Gravatar image
Ambalus
  • Репутация: 3
  • Сниппеты : 2
  • Ревизии : 0
Подписаться

Чтобы увидеть комментарии, нужно быть участником сообщества

Регистрация