Преобразование даты JDE Julian в григорианский

Я пытаюсь преобразовать даты JDE и собрал большое количество информации, и решил, что я попытаюсь сделать функцию преобразования SQL, чтобы упростить некоторые задачи.

Вот функция, которую я придумал, которую я просто называю «ToGregorian»

CREATE FUNCTION [dbo].[ToGregorian](@julian varchar(6)) RETURNS datetime AS BEGIN DECLARE @datetime datetime SET @datetime = CAST(19+CAST(SUBSTRING(@julian, 1, 1) as int) as varchar(4))+SUBSTRING(@julian, 2,2)+'-01-01' SET @datetime = DATEADD(day, CAST(SUBSTRING(@julian, 4,3) as int)-1, @datetime) RETURN @datetime END 
  1. Принимает «юлианскую» строку.
  2. Принимает первое письмо и добавляет его в век, начиная с 19-го.
  3. Добавляет десятилетия и годы от следующих двух символов.
  4. Наконец добавляются дни, которые являются последними 3 символами, и вычитают 1, поскольку он уже имел 1 день в первой настройке. (например, 2011-01-01)
  5. Результат: 111186 => 2011-07-05 00:00:00.000

По-моему, это немного неуклюжий и перебор, и я надеюсь, что есть лучший способ сделать это. Возможно, я делаю слишком много конверсий или, может быть, я должен использовать другой метод alltogether?

Любые советы по улучшению функции?
Может быть, другой, лучший, метод?
Не было бы против, если бы оно было более читаемым …

У меня также есть встроенная версия, где, если, например, у меня есть только привилегии чтения и я не могу использовать функции, которые также выглядят беспорядочными, возможно ли сделать их более читабельными или лучше?

 CAST(REPLACE(Convert(VARCHAR, DATEADD(d,CAST(SUBSTRING(CAST([column] AS VARCHAR), 4,3) AS INT)-1, CAST(CAST(19+CAST(SUBSTRING(CAST([column] AS VARCHAR), 1,1) AS INT) AS VARCHAR)+SUBSTRING(CAST([column] AS VARCHAR), 2,2) + '-01-01' AS DATETIME)), 111), '/', '-') AS DATETIME) 

Я думаю, что более эффективно использовать собственную математику дат-времени, чем все это переключение в разные строковые, датские и числовые форматы.

 DECLARE @julian VARCHAR(6) = '111186'; SELECT DATEADD(YEAR, 100*CONVERT(INT, LEFT(@julian,1)) +10*CONVERT(INT, SUBSTRING(@julian, 2,1)) +CONVERT(INT, SUBSTRING(@julian,3,1)), DATEADD(DAY, CONVERT(INT,SUBSTRING(@julian, 4, 3))-1, 0)); 

Результат:

 =================== 2011-07-05 00:00:00 

Предполагая, что эти данные не часто меняются, может быть гораздо эффективнее фактически хранить дату в качестве вычисленного столбца (именно поэтому я выбрал базовую дату 0 вместо некоторого строкового представления, что вызовет проблемы детерминизма, предотвращающие столбцы от сохраняются и потенциально индексируются).

 CREATE TABLE dbo.JDEDates ( JDEDate VARCHAR(6), GregorianDate AS CONVERT(SMALLDATETIME, DATEADD(YEAR, 100*CONVERT(INT, LEFT(RIGHT('0'+JDEDate,6),1)) +10*CONVERT(INT, SUBSTRING(RIGHT('0'+JDEDate,6), 2,1)) +CONVERT(INT, SUBSTRING(RIGHT('0'+JDEDate,6),3,1)), DATEADD(DAY, CONVERT(INT, RIGHT(JDEDate, 3))-1, 0)) ) PERSISTED ); INSERT dbo.JDEDates(JDEDate) SELECT '111186'; SELECT JDEDate, GregorianDate FROM dbo.JDEDates; 

Результаты:

 JDEDate GregorianDate ======= =================== 111186 2011-07-05 00:00:00 

Даже если вы не индексируете столбец, он по-прежнему скрывает уродливый расчет от вас, поскольку вы настаиваете, что платите только за время записи, так как это не приводит к тому, что вы выполняете дорогостоящие функциональные операции во время запроса, когда этот столбец ссылается …

Принятый ответ неверен. Он не сможет дать правильный ответ за 116060, который должен быть 29 февраля 2016 года. Вместо этого он возвращается 1 марта 2016 года.

Кажется, что JDE хранит даты как целые числа, поэтому вместо преобразования из строк я всегда перехожу прямо из целого числа:

 DATEADD(DAY, @Julian % 1000, DATEADD(YEAR, @Julian / 1000, '31-dec-1899')) 

Чтобы перейти от варчара (6), я использую:

 DATEADD(DAY, CAST(RIGHT(@Julian,3) AS int), DATEADD(YEAR, CAST(LEFT(@Julian,LEN(@Julian)-3) AS int), '31-dec-1899')) 

DATE (CHAR (1900000 + GLDGJ)), где GLDGJ – это значение юлианской даты

 USE [master] GO /****** Object: UserDefinedFunction [dbo].[ToGregorian] Script Date: 08/18/2015 14:33:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[ToGregorian](@julian varchar(6),@time varchar(6)) RETURNS datetime AS BEGIN DECLARE @datetime datetime,@hour int, @minute int, @second int set @time = ltrim(rtrim(@time)); set @julian = ltrim(rtrim(@julian)); if(LEN(@julian) = 5) set @julian = '0' + @julian IF(LEN(@time) = 6) BEGIN SET @hour = Convert(int,LEFT(@time,2)); SET @minute = CONVERT(int,Substring(@time,3,2)); SET @second = CONVERT(int,Substring(@time,5,2)); END else IF(LEN(@time) = 5) BEGIN SET @hour = Convert(int,LEFT(@time,1)); SET @minute = CONVERT(int,Substring(@time,2,2)); SET @second = CONVERT(int,Substring(@time,4,2)); END else IF(LEN(@time) = 4) BEGIN SET @hour = 0; SET @minute = CONVERT(int,LEFT(@time,2)); SET @second = CONVERT(int,Substring(@time,3,2)); END else IF(LEN(@time) = 3) BEGIN SET @hour = 0; SET @minute = CONVERT(int,LEFT(@time,1)); SET @second = CONVERT(int,Substring(@time,2,2)); END else BEGIN SET @hour = 0; SET @minute = 0; SET @second = @time; END SET @datetime = DATEADD(YEAR,100*CONVERT(INT, LEFT(@julian,1))+10*CONVERT(INT, SUBSTRING(@julian, 2,1))+CONVERT(INT, SUBSTRING(@julian,3,1)),0); SET @datetime = DATEADD(DAY, CONVERT(INT,SUBSTRING(@julian, 4, 3))-1,@datetime); SET @datetime = DATEADD(hour,@hour,@datetime) SET @datetime = DATEADD(minute,@minute,@datetime); SET @datetime = DATEADD(second,@second,@datetime); RETURN @datetime END