Преобразовать номер дня в году в дату

Question

В БД greenplum есть поле с типом строка, которое можно привести к типу таймстэмп используя следующую конструкцию to_timestamp('202409676543', 'YYYYDDDSSSSSFF6') Число тут рандомное вбил, главное, что из 12 символов. YYYY это понятно год, DDD я так понимаю номер дня в году, SSSSS - секунд с начала суток? FF6 - тоже что то с миллисекундами. Так вот задача подобное же преобразование сделать на ms SQL server. Если даже не считать секунды, то как минимум, как можно получить месяц, день из просто номера дня в году? В MySQL к примеру по видимому имеется для этого формула makedate.

Answer 1

Можно так:

SELECT DATEADD(day,CAST(RIGHT('2024302',3) AS INT),
    DATEFROMPARTS(CAST(LEFT('2024302',4) AS INT)-1,12,31))

2024-10-18

Answer 2

Могу сказать, что очень специфичное преобразование даты. В основном строят либо стандартно по формату ODBC yyyy-MM-dd HH:mm:ss.fff, либо Unix Timestamp. И этого хватает для передачи даты между большинством программ/систем.

Есть функция 'FORMAT' в MSSQL. Но насколько я помню, преобразовывает только из даты в строку, а обратно - нет. Скорее всего нужно написать определенный преобразователь прямо в запросе только под один конкретный формат. Что-то вроде такого:

SELECT
  DATEADD(
    [MICROSECOND],
    tt.[FF6],
    DATEADD(
      [SECOND],
      tt.[SSSSS],
      DATEADD(
        [DAY],
        tt.[DDD] - 1,
        CONVERT([datetime2], tt.[YYYY] + '-01-01', 120)
      )
    )
  )
FROM
  (
    SELECT
      t.[FormattedDate],
      SUBSTRING(t.[FormattedDate], 1, 4) AS [YYYY],
      CONVERT([smallint], SUBSTRING(t.[FormattedDate], 5, 3)) AS [DDD],
      CONVERT([int], SUBSTRING(t.[FormattedDate], 8, 5)) AS [SSSSS],
      CONVERT([int], SUBSTRING(t.[FormattedDate], 13, 6)) AS [FF6]
    FROM
      (
        SELECT 
          '202409676543000043' AS [FormattedDate]
      ) t
  ) tt

А вот универсальный парсер может сильно ударить по производительности. В принципе, наподобие можно и самому накидать (не факт, что работает правильно):

CREATE FUNCTION [dbo].[CUSTOMDATEFORMAT](
  @InputString [varchar](254),
  @FormatString [varchar](254)
)
RETURNS [datetime2]
BEGIN
  IF @InputString IS NULL
  BEGIN
    RETURN NULL;
  END
  IF @FormatString IS NULL
  BEGIN
    RETURN NULL;
  END

  SET @InputString = LTRIM(RTRIM(@InputString));
  SET @FormatString = LTRIM(RTRIM(@FormatString));
  DECLARE @InputStringLength [tinyint] = LEN(@InputString);
  DECLARE @FormatStringLength [tinyint] = LEN(@FormatString);
  IF LEN(REPLACE(@FormatString, '\', '')) = 0
  BEGIN
    RETURN NULL;
  END

  -- types:
  --  0 - custom char
  --  1 - year
  --  2 - month
  --  3 - day
  --  4 - hours
  --  5 - minutes
  --  6 - seconds
  --  7 - subseconds
  --  8 - am/pm designator
  --  9 - time zone info
  DECLARE @CharPositions TABLE (
    [Type] [tinyint] NOT NULL,
    [Position] [tinyint] NOT NULL,
    [Length] [tinyint] NOT NULL
  );

  DECLARE @InputStringPos [tinyint] = 0;
  DECLARE @CurrentPos [tinyint] = 0;
  DECLARE @CurrentChar [char](1) = '';
  DECLARE @CurrentType [tinyint] = 0;
  DECLARE @CurrentLength [tinyint] = 0;

  DECLARE @PrevPos [tinyint] = 0;
  DECLARE @PrevChar [char](1) = '';
  DECLARE @PrevType [tinyint] = 0;
  DECLARE @PrevLength [tinyint] = 0;
  WHILE 1 = 1
  BEGIN
    SET @CurrentPos = @CurrentPos + 1;
    IF @CurrentPos > @FormatStringLength
    BEGIN
      INSERT INTO @CharPositions VALUES (@CurrentType, @PrevPos, @CurrentLength);
      BREAK;
    END

    SELECT @CurrentChar = SUBSTRING(@FormatString, @CurrentPos, 1);
    IF @CurrentChar IS NULL
    BEGIN
      CONTINUE;
    END

    IF @PrevChar = '\'
    BEGIN
      SET @CurrentType = 0;
    END
    ELSE
    BEGIN
      IF @CurrentChar = '' OR @CurrentChar = '\' SET @CurrentType = 0;
      -- using COLLATE to compare
      --  _CI_AS - case insensetive
      --  _CS_AS - case sensetive
      -- change Cyrillic_General if database uses another collation
      ELSE IF @CurrentChar = 'y' COLLATE Cyrillic_General_CI_AS SET @CurrentType = 1;
      ELSE IF @CurrentChar = 'M' COLLATE Cyrillic_General_CS_AS SET @CurrentType = 2;
      ELSE IF @CurrentChar = 'd' COLLATE Cyrillic_General_CI_AS SET @CurrentType = 3;
      ELSE IF @CurrentChar = 'h' COLLATE Cyrillic_General_CI_AS SET @CurrentType = 4;
      ELSE IF @CurrentChar = 'm' COLLATE Cyrillic_General_CS_AS SET @CurrentType = 5;
      ELSE IF @CurrentChar = 's' COLLATE Cyrillic_General_CI_AS SET @CurrentType = 6;
      ELSE IF @CurrentChar = 'f' COLLATE Cyrillic_General_CI_AS SET @CurrentType = 7;
      ELSE IF @CurrentChar = 't' COLLATE Cyrillic_General_CI_AS SET @CurrentType = 8;
      ELSE IF @CurrentChar = 'z' COLLATE Cyrillic_General_CI_AS SET @CurrentType = 9;
      ELSE SET @CurrentType = 0;
      SET @InputStringPos = @InputStringPos + 1;
    END

    IF @CurrentPos = 1
    BEGIN
      SET @PrevType = @CurrentType;
      SET @PrevPos = 1;
      SET @PrevChar = @CurrentChar;
      SET @CurrentLength = 1;
      CONTINUE;
    END 
    ELSE IF @CurrentType = @PrevType
    BEGIN
      SET @CurrentLength = @CurrentLength + 1;
    END
    ELSE
    BEGIN
      SET @PrevLength = @CurrentLength;
      SET @CurrentLength = 1;
      INSERT INTO @CharPositions VALUES (@PrevType, @PrevPos, @PrevLength);
      SET @PrevType = @CurrentType;
      SET @PrevPos = @InputStringPos;
    END

    SET @PrevChar = @CurrentChar;
  END

  IF EXISTS (
    SELECT 1
    FROM @CharPositions cp
    WHERE
      cp.[Type] > 0
    GROUP BY cp.[Type]
    HAVING COUNT(*) > 1
  )
  BEGIN
    RETURN NULL;
  END

  DECLARE @FictiveDate [datetime];
  DECLARE @DaysOfWeek TABLE (
    [Num] [tinyint] NOT NULL,
    [ShortName] [varchar](4) NOT NULL,
    [FullName] [varchar](32) NOT NULL
  );
  DECLARE @DayNumOfWeek [tinyint] = 0;
  WHILE @DayNumOfWeek < 7
  BEGIN
    SET @DayNumOfWeek = @DayNumOfWeek + 1;
    SET @FictiveDate = CONVERT([datetime], '1753-01-' + RIGHT(CONVERT([varchar](2), @DayNumOfWeek), 2), 120);
    INSERT INTO @DaysOfWeek
    VALUES (
      DATEPART([WEEKDAY], @FictiveDate),
      FORMAT(@FictiveDate, 'ddd'),
      FORMAT(@FictiveDate, 'dddd')
    );
  END
  DECLARE @Months TABLE (
    [Num] [tinyint] NOT NULL,
    [ShortName] [varchar](4) NOT NULL,
    [FullName] [varchar](32) NOT NULL
  );
  DECLARE @MonthNum [tinyint] = 0;
  WHILE @MonthNum < 12
  BEGIN
    SET @MonthNum = @MonthNum + 1;
    SET @FictiveDate = CONVERT([datetime], '1753-' + RIGHT('0' + CONVERT([varchar](2), @MonthNum), 2) + '-01', 120)
    INSERT INTO @Months 
    VALUES (
      @MonthNum,
      FORMAT(@FictiveDate, 'MMM'),
      FORMAT(@FictiveDate, 'MMMM')
    );
  END

  DECLARE @FirstMonthOrDay [tinyint];
  SELECT TOP 1
    @FirstMonthOrDay = cp.[Type]
  FROM
    @CharPositions cp
  WHERE
    cp.[Type] IN (2, 3)
  ORDER BY
    cp.[Position];

  DECLARE @DayFormat [varchar](254);
  DECLARE @DayPosition [tinyint];
  DECLARE @DayLength [tinyint];
  DECLARE @DaySample [smallint];

  IF @FirstMonthOrDay = 3
  BEGIN
    SELECT
      @DayPosition = cp.[Position],
      @DayLength = cp.[Length]
    FROM
      @CharPositions cp
    WHERE
      cp.[Type] = 3;
  
    IF @DayLength BETWEEN 1 AND 2
    BEGIN
      SET @DayFormat = SUBSTRING(@InputString, @DayPosition, @DayLength);
      SET @DaySample = TRY_CONVERT([smallint], @DayFormat);
      IF @DaySample IS NULL
      BEGIN
        RETURN NULL;
      END
    END
    ELSE IF @DayLength > 2
    BEGIN
      SET @DayFormat = SUBSTRING(@InputString, @DayPosition, @InputStringLength - @DayPosition + 1);
      IF @DayLength = 3
      BEGIN
        SELECT
          @DaySample = d.[Num],
          @DayLength = LEN(d.[ShortName])
        FROM @DaysOfWeek d
        WHERE
          @DayFormat LIKE d.[ShortName] + '%';

        IF @DaySample IS NULL
        BEGIN
          SET @DayFormat = SUBSTRING(@InputString, @DayPosition, @DayLength);
          SET @DaySample = TRY_CONVERT([smallint], @DayFormat);
          IF @DaySample IS NULL
          BEGIN
            RETURN NULL;
          END
        END
        ELSE
        BEGIN
          UPDATE @CharPositions
          SET
            [Position] = [Position] + (@DayLength - 3) 
          WHERE
            [Position] > @DayPosition;
        END
      END
      ELSE IF @DayLength = 4
      BEGIN
        SELECT
          @DaySample = d.[Num],
          @DayLength = LEN(d.[FullName])
        FROM @DaysOfWeek d
        WHERE
          @DayFormat LIKE d.[FullName] + '%';

        IF @DaySample IS NULL
        BEGIN
          SET @DayFormat = SUBSTRING(@InputString, @DayPosition, @DayLength);
          SET @DaySample = TRY_CONVERT([smallint], @DayFormat);
          IF @DaySample IS NULL
          BEGIN
            RETURN NULL;
          END
        END
        ELSE
        BEGIN
          UPDATE @CharPositions
          SET
            [Position] = [Position] + (@DayLength - 4) 
          WHERE
            [Position] > @DayPosition;
        END
      END
      ELSE
      BEGIN
        RETURN NULL;
      END
    END
  END

  DECLARE @MonthFormat [varchar](254);
  DECLARE @MonthPosition [tinyint];
  DECLARE @MonthLength [tinyint];
  DECLARE @Month [tinyint];
  SELECT
    @MonthPosition = cp.[Position],
    @MonthLength = cp.[Length]
  FROM
    @CharPositions cp
  WHERE
    cp.[Type] = 2;
  IF @MonthLength BETWEEN 1 AND 2
  BEGIN
    SET @MonthFormat = RIGHT(SUBSTRING(@InputString, @MonthPosition, @MonthLength), 2);
    SET @Month = TRY_CONVERT([tinyint], @MonthFormat);
    IF @Month IS NULL OR @Month NOT BETWEEN 1 AND 12
    BEGIN
      RETURN NULL;
    END
  END
  ELSE IF @MonthLength > 2
  BEGIN
    SET @MonthFormat = SUBSTRING(@InputString, @MonthPosition, @InputStringLength - @MonthPosition + 1);
    IF @MonthLength = 3
    BEGIN
      SELECT
        @Month = m.[Num],
        @MonthLength = LEN(m.[ShortName])
      FROM @Months m
      WHERE
        @MonthFormat LIKE m.[ShortName] + '%';
      UPDATE @CharPositions
      SET
        [Position] = [Position] + (@MonthLength - 3) 
      WHERE
        [Position] > @MonthPosition;
    END
    ELSE IF @MonthLength = 4
    BEGIN
      SELECT
        @Month = m.[Num],
        @MonthLength = LEN(m.[FullName])
      FROM @Months m
      WHERE
        @MonthFormat LIKE m.[FullName] + '%';
      UPDATE @CharPositions
      SET
        [Position] = [Position] + (@MonthLength - 4) 
      WHERE
        [Position] > @MonthPosition;
    END

    IF @Month IS NULL
    BEGIN
      RETURN NULL;
    END
  END

  IF @FirstMonthOrDay = 2
  BEGIN
    SELECT
      @DayPosition = cp.[Position],
      @DayLength = cp.[Length]
    FROM
      @CharPositions cp
    WHERE
      cp.[Type] = 3;
  
    IF @DayLength BETWEEN 1 AND 2
    BEGIN
      SET @DayFormat = SUBSTRING(@InputString, @DayPosition, @DayLength);
      SET @DaySample = TRY_CONVERT([smallint], @DayFormat);
      IF @DaySample IS NULL
      BEGIN
        RETURN NULL;
      END
    END
    ELSE IF @DayLength > 2
    BEGIN
      SET @DayFormat = SUBSTRING(@InputString, @DayPosition, @InputStringLength - @DayPosition + 1);
      IF @DayLength = 3
      BEGIN
        SELECT
          @DaySample = d.[Num],
          @DayLength = LEN(d.[ShortName])
        FROM @DaysOfWeek d
        WHERE
          @DayFormat LIKE d.[ShortName] + '%';

        IF @DaySample IS NULL
        BEGIN
          SET @DayFormat = SUBSTRING(@InputString, @DayPosition, @DayLength);
          SET @DaySample = TRY_CONVERT([smallint], @DayFormat);
          IF @DaySample IS NULL
          BEGIN
            RETURN NULL;
          END
        END
        ELSE
        BEGIN
          UPDATE @CharPositions
          SET
            [Position] = [Position] + (@DayLength - 3) 
          WHERE
            [Position] > @DayPosition;
        END
      END
      ELSE IF @DayLength = 4
      BEGIN
        SELECT
          @DaySample = d.[Num],
          @DayLength = LEN(d.[FullName])
        FROM @DaysOfWeek d
        WHERE
          @DayFormat LIKE d.[FullName] + '%';

        IF @DaySample IS NULL
        BEGIN
          SET @DayFormat = SUBSTRING(@InputString, @DayPosition, @DayLength);
          SET @DaySample = TRY_CONVERT([smallint], @DayFormat);
          IF @DaySample IS NULL
          BEGIN
            RETURN NULL;
          END
        END
        ELSE
        BEGIN
          UPDATE @CharPositions
          SET
            [Position] = [Position] + (@DayLength - 4) 
          WHERE
            [Position] > @DayPosition;
        END
      END
      ELSE
      BEGIN
        RETURN NULL;
      END
    END
  END

  DECLARE @YearString [varchar](5);
  DECLARE @YearLength [tinyint];
  DECLARE @Year [smallint];
  SELECT
    @YearString = t.[YearString],
    @YearLength = t.[Length],
    @Year = TRY_CONVERT([smallint], t.[YearString])
  FROM
    (
      SELECT
        cp.[Length],
        RIGHT(SUBSTRING(@InputString, cp.[Position], cp.[Length]), 5) AS [YearString]
      FROM
        @CharPositions cp
      WHERE
        cp.[Type] = 1
    ) t;
  IF @YearLength = 2
  BEGIN
    DECLARE @CurrentEpoch [smallint] = TRY_CONVERT([smallint], LEFT(CONVERT([varchar](4), YEAR(GETDATE())), 2));
    IF @Year >= 50 AND @CurrentEpoch > 0
    BEGIN
      SET @CurrentEpoch = @CurrentEpoch - 1;
    END
    SET @Year = TRY_CONVERT([smallint], CONVERT([varchar](2), @CurrentEpoch) + RIGHT('00' + CONVERT([varchar](2), @Year), 2));
  END

  DECLARE @AMPMDesignator [varchar](2);
  DECLARE @AMPMDesignatorDayNight [tinyint];
  SELECT
    @AMPMDesignatorDayNight = CASE
      WHEN t.[AMPMString] = 'AM' THEN 1
      WHEN t.[AMPMString] = 'PM' THEN 2
      ELSE 0
    END
  FROM
    (
      SELECT
        RIGHT(SUBSTRING(@InputString, cp.[Position], cp.[Length]), 2) AS [AMPMString]
      FROM
        @CharPositions cp
      WHERE
        cp.[Type] = 8
    ) t;

  DECLARE @HourString [varchar](4);
  DECLARE @HourLength [tinyint];
  DECLARE @Hour [smallint];
  SELECT
    @HourString = t.[HourString],
    @HourLength = t.[Length],
    @Hour = TRY_CONVERT([smallint], t.[HourString])
  FROM
    (
      SELECT
        cp.[Length],
        RIGHT(SUBSTRING(@InputString, cp.[Position], cp.[Length]), 4) AS [HourString]
      FROM
        @CharPositions cp
      WHERE
        cp.[Type] = 4
    ) t;

  DECLARE @MinutesString [varchar](4);
  DECLARE @MinutesLength [tinyint];
  DECLARE @Minutes [smallint];
  SELECT
    @MinutesString = t.[MinutesString],
    @MinutesLength = t.[Length],
    @Minutes = TRY_CONVERT([smallint], t.[MinutesString])
  FROM
    (
      SELECT
        cp.[Length],
        RIGHT(SUBSTRING(@InputString, cp.[Position], cp.[Length]), 4) AS [MinutesString]
      FROM
        @CharPositions cp
      WHERE
        cp.[Type] = 5
    ) t;

  DECLARE @SecondsString [varchar](5);
  DECLARE @SecondsLength [tinyint];
  DECLARE @Seconds [int];
  SELECT
    @SecondsString = t.[SecondsString],
    @SecondsLength = t.[Length],
    @Seconds = TRY_CONVERT([int], t.[SecondsString])
  FROM
    (
      SELECT
        cp.[Length],
        RIGHT(SUBSTRING(@InputString, cp.[Position], cp.[Length]), 5) AS [SecondsString]
      FROM
        @CharPositions cp
      WHERE
        cp.[Type] = 6
    ) t;

  DECLARE @SubsecondsString [varchar](7);
  DECLARE @SubsecondsLength [tinyint];
  DECLARE @Subseconds [int];
  SELECT
    @SubsecondsString = t.[SubsecondsString],
    @SubsecondsLength = t.[Length],
    @Subseconds = TRY_CONVERT([int], t.[SubsecondsString])
  FROM
    (
      SELECT
        cp.[Length],
        RIGHT(SUBSTRING(@InputString, cp.[Position], cp.[Length]), 7) AS [SubsecondsString]
      FROM
        @CharPositions cp
      WHERE
        cp.[Type] = 7
    ) t;

  DECLARE @TimeZoneString [varchar](6);
  DECLARE @TimeZoneSign [varchar](1);
  DECLARE @TimeZoneSeparatorIndex [tinyint];
  DECLARE @TimeZoneHoursString [varchar](2);
  DECLARE @TimeZoneHours [tinyint];
  DECLARE @TimeZoneMinutesString [varchar](2);
  DECLARE @TimeZoneMinutes [tinyint];
  SELECT
    @TimeZoneSign = SUBSTRING(@InputString, cp.[Position], 1),
    @TimeZoneString = RIGHT(SUBSTRING(@InputString, cp.[Position] + 1, 5), 5)
  FROM
    @CharPositions cp
  WHERE
    cp.[Type] = 9;
  IF @TimeZoneString IS NOT NULL AND LEN(@TimeZoneString) > 0
  BEGIN
    IF SUBSTRING(@TimeZoneString, 2, 1) = ':'
    BEGIN
      SET @TimeZoneHoursString = '0' + SUBSTRING(@TimeZoneString, 1, 1);
      SET @TimeZoneMinutesString = SUBSTRING(@TimeZoneString, 3, 2);
    END
    ELSE IF SUBSTRING(@TimeZoneString, 3, 1) = ':'
    BEGIN
      SET @TimeZoneHoursString = SUBSTRING(@TimeZoneString, 1, 2);
      SET @TimeZoneMinutesString = SUBSTRING(@TimeZoneString, 4, 2);
    END
    SET @TimeZoneSign = CASE WHEN @TimeZoneSign IN ('+', '-') THEN @TimeZoneSign ELSE NULL END;
    SET @TimeZoneHours = TRY_CONVERT([tinyint], @TimeZoneHoursString);
    SET @TimeZoneMinutes = TRY_CONVERT([tinyint], @TimeZoneMinutesString);
  END

  DECLARE @OutputDate [datetime2];
  IF @Year IS NULL
  BEGIN
    SET @Year = 1753;
  END

  SET @OutputDate = CONVERT([datetime2], CONVERT([varchar](4), @Year) + '-01-01', 120);

  DECLARE @HoursSet [bit] = 0;
  IF @Month IS NULL
  BEGIN
    IF @DayLength > 2 AND @DaySample BETWEEN 1 AND 366
    BEGIN
      SET @OutputDate = DATEADD([DAY], @DaySample - 1, @OutputDate);
      IF YEAR(@OutputDate) > @Year
      BEGIN
        RETURN NULL;
      END
    END
    ELSE IF @DaySample IS NULL AND @HourLength = 4 AND @Hour BETWEEN 0 AND 8783 AND ISNULL(@AMPMDesignatorDayNight, 0) = 0
    BEGIN
      SET @OutputDate = DATEADD([HOUR], @Hour, @OutputDate);
      IF YEAR(@OutputDate) > @Year
      BEGIN
        RETURN NULL;
      END
      SET @HoursSet = 1;
    END
  END
  ELSE IF @Month BETWEEN 1 AND 12
  BEGIN
    SET @OutputDate = DATEADD([MONTH], @Month - 1, @OutputDate);
    IF @DayLength BETWEEN 1 AND 2 AND @DaySample BETWEEN 1 AND 31
    BEGIN
      SET @OutputDate = DATEADD([DAY], @DaySample - 1, @OutputDate);
      IF MONTH(@OutputDate) > @Month
      BEGIN
        RETURN NULL;
      END
    END
    ELSE IF @DayLength > 2 AND @DaySample BETWEEN 1 AND 7
    BEGIN
      DECLARE @MonthStartDayWeek [smallint] = DATEPART([WEEKDAY], @OutputDate) - 1;
      DECLARE @TargetDayWeek [smallint] = @DaySample - 1;
      DECLARE @DayWeekDiff [smallint];
      IF @TargetDayWeek >= @MonthStartDayWeek
      BEGIN
        SET @DayWeekDiff = ABS(@MonthStartDayWeek - @TargetDayWeek);
      END
      ELSE
      BEGIN
        SET @DayWeekDiff = (7 - @MonthStartDayWeek) + @TargetDayWeek;
      END
      SET @OutputDate = DATEADD([DAY], @DayWeekDiff, @OutputDate);
    END
    ELSE IF @DaySample IS NULL AND @HourLength = 4 AND @Hour BETWEEN 0 AND 8783 AND ISNULL(@AMPMDesignatorDayNight, 0) = 0
    BEGIN
      SET @OutputDate = DATEADD([HOUR], @Hour, @OutputDate);
      IF YEAR(@OutputDate) > @Year
      BEGIN
        RETURN NULL;
      END
      SET @HoursSet = 1;
    END
  END
  ELSE
  BEGIN
    RETURN NULL;
  END

  DECLARE @SecondsSet [bit] = 0;
  IF @HoursSet = 1
  BEGIN
    IF @Minutes BETWEEN 0 AND 59
    BEGIN
      SET @OutputDate = DATEADD([MINUTE], @Minutes, @OutputDate);
      IF @Seconds BETWEEN 0 AND 59
      BEGIN
        SET @OutputDate = DATEADD([SECOND], @Seconds, @OutputDate);
        SET @SecondsSet = 1;
      END
    END
    ELSE IF @Minutes IS NULL AND @SecondsLength = 4 AND @Seconds BETWEEN 0 AND 3599
    BEGIN
      SET @OutputDate = DATEADD([SECOND], @Seconds, @OutputDate);
      SET @SecondsSet = 1;
    END
  END
  ELSE
  BEGIN
    IF @Hour BETWEEN 0 AND 23
    BEGIN
      IF @AMPMDesignatorDayNight > 0
      BEGIN
        IF @Hour BETWEEN 1 AND 12
        BEGIN
          IF @AMPMDesignatorDayNight = 1
          BEGIN
            IF @Hour = 12
            BEGIN
              SET @Hour = 0;
            END
          END
          ELSE IF @AMPMDesignatorDayNight = 2
          BEGIN
            IF @Hour = 12
            BEGIN
              SET @Hour = 12;
            END
            ELSE
            BEGIN
              SET @Hour = @Hour + 12;
            END
          END
          ELSE
          BEGIN
            RETURN NULL;
          END
        END
      END
      SET @OutputDate = DATEADD([HOUR], @Hour, @OutputDate);
      IF @Minutes BETWEEN 0 AND 59
      BEGIN
        SET @OutputDate = DATEADD([MINUTE], @Minutes, @OutputDate);
        IF @Seconds BETWEEN 0 AND 59
        BEGIN
          SET @OutputDate = DATEADD([SECOND], @Seconds, @OutputDate);
          SET @SecondsSet = 1;
        END
      END
      ELSE IF @Minutes IS NULL AND @SecondsLength = 4 AND @Seconds BETWEEN 0 AND 3599
      BEGIN
        SET @OutputDate = DATEADD([SECOND], @Seconds, @OutputDate);
        SET @SecondsSet = 1;
      END
    END
    ELSE IF @Hour IS NULL AND @MinutesLength = 4 AND @Minutes BETWEEN 0 AND 1439
    BEGIN
      SET @OutputDate = DATEADD([MINUTE], @Minutes, @OutputDate);
      IF @Seconds BETWEEN 0 AND 59
      BEGIN
        SET @OutputDate = DATEADD([SECOND], @Seconds, @OutputDate);
        SET @SecondsSet = 1;
      END
    END
    ELSE IF @Hour IS NULL AND @Minutes IS NULL AND @SecondsLength = 5 AND @Seconds BETWEEN 0 AND 86399
    BEGIN
      SET @OutputDate = DATEADD([SECOND], @Seconds, @OutputDate);
      SET @SecondsSet = 1;
    END
  END

  IF @SecondsSet = 1
  BEGIN
    IF @SubsecondsLength = 1
    BEGIN
      SET @OutputDate = DATEADD([MILLISECOND], @Subseconds * 100, @OutputDate);
    END
    ELSE IF @SubsecondsLength = 2
    BEGIN
      SET @OutputDate = DATEADD([MILLISECOND], @Subseconds * 10, @OutputDate);
    END
    ELSE IF @SubsecondsLength = 3
    BEGIN
      SET @OutputDate = DATEADD([MILLISECOND], @Subseconds, @OutputDate);
    END
    ELSE IF @SubsecondsLength = 4
    BEGIN
      SET @OutputDate = DATEADD([MICROSECOND], @Subseconds * 100, @OutputDate);
    END
    ELSE IF @SubsecondsLength = 5
    BEGIN
      SET @OutputDate = DATEADD([MICROSECOND], @Subseconds * 10, @OutputDate);
    END
    ELSE IF @SubsecondsLength = 6
    BEGIN
      SET @OutputDate = DATEADD([MICROSECOND], @Subseconds, @OutputDate);
    END
    ELSE IF @SubsecondsLength = 7
    BEGIN
      DECLARE @FullSubSecond [int] = @Subseconds * 100;
      SET @OutputDate = DATEADD([NANOSECOND], @FullSubSecond, @OutputDate);
    END
  END

  IF @TimeZoneHours BETWEEN 0 AND 23
  BEGIN
    IF @TimeZoneSign = '-'
    BEGIN
      SET @OutputDate = DATEADD([HOUR], -@TimeZoneHours, @OutputDate);
      IF @TimeZoneMinutes BETWEEN 0 AND 59
      BEGIN
        SET @OutputDate = DATEADD([MINUTE], -@TimeZoneMinutes, @OutputDate);
      END
    END
    ELSE
    BEGIN
      SET @OutputDate = DATEADD([HOUR], @TimeZoneHours, @OutputDate);
      IF @TimeZoneMinutes BETWEEN 0 AND 59
      BEGIN
        SET @OutputDate = DATEADD([MINUTE], @TimeZoneMinutes, @OutputDate);
      END
    END
  END

  --SELECT @Year, @Month, @DayLength, @DaySample, @AMPMDesignatorDayNight, @Hour, @Minutes, @Seconds, @Subseconds, @TimeZoneSign, @TimeZoneHours, @TimeZoneMinutes
  RETURN @OutputDate;
END

Которую можно уже ручками крутить-вертеть, чтобы принимал на вход разного рода форматов и выводил то что нужно:

SELECT
  [dbo].[CUSTOMDATEFORMAT]('202409676543000043', 'YYYYDDDSSSSSFFFFFF'),
  [dbo].[CUSTOMDATEFORMAT]('2024-10-19 23:42:43.466', 'yyyy-MM-dd HH:mm:ss.fff'),
  [dbo].[CUSTOMDATEFORMAT]('16:43', 'HH:mm'),
  [dbo].[CUSTOMDATEFORMAT]('2024 Oct 19', 'yyyy MMM dd'),
  [dbo].[CUSTOMDATEFORMAT]('2024 October Wednesday', 'yyyy MMMM dddd'),
  [dbo].[CUSTOMDATEFORMAT]('2024 Wednesday October 23:50+07:00', 'yyyy dddd MMMM hh:mmZ'),
  [dbo].[CUSTOMDATEFORMAT]('01/01/60 04:56 PM', 'dd/MM/yy HH:mm TT'),
  [dbo].[CUSTOMDATEFORMAT]('04.06.2000', 'dd.MM.yyyy'),
  [dbo].[CUSTOMDATEFORMAT]('19365', 'yyddd'),
  [dbo].[CUSTOMDATEFORMAT]('2024302', 'yyyyddd'),
  [dbo].[CUSTOMDATEFORMAT]('YEAR 2024 MONTH 10 DAY 15 HOUR 10 MINUTE 16 SECONDS 22', '\YEAR yyyy \MON\T\H MM \DA\Y dd \HOUR HH \MINU\TE mm \SECON\D\S ss');

1	2	3	4	5	6	7	8	9	10	11
2024-04-05 21:15:43.0000430	2024-10-19 23:42:43.4660000	1753-01-01 16:43:00.0000000	2024-10-19 00:00:00.0000000	2024-10-02 00:00:00.0000000	2024-10-03 06:50:00.0000000	1960-01-01 16:56:00.0000000	2000-06-04 00:00:00.0000000	2019-12-31 00:00:00.0000000	2024-10-28 00:00:00.0000000	2024-10-15 10:16:22.0000000

БЛОГ НА HUSL

Преобразовать номер дня в году в дату

Ответы (2 шт):