UDF to convert SQL Server Date/Time to Date/Time

agent-datetime.sql

/*
Converts SQL Server Agent Time to Time
*/
CREATE FUNCTION dbo.fnc_agentTimeToTime (
	@agent_date INT
)
RETURNS TIME(0)
AS
BEGIN
    DECLARE @result TIME(0);
    DECLARE @casted VARCHAR(6);

	IF (@agent_date IS NULL) BEGIN
        SET @result = NULL;
    END
    ELSE BEGIN
        SET @casted = RIGHT('000000' + CONVERT(VARCHAR(8), @agent_date), 6);
        SET @result = TIMEFROMPARTS (
            CONVERT(INT, SUBSTRING(@casted, 1, 2))
          , CONVERT(INT, SUBSTRING(@casted, 3, 2))
          , CONVERT(INT, SUBSTRING(@casted, 5, 2))
          , 0
          , 0
        );
    END;
      
    RETURN @result;  
END
GO


/*
Converts SQL Server Agent Date to Date
*/
CREATE FUNCTION dbo.fnc_agentDateToDate (
	@agent_date INT
)
RETURNS DATE
AS
BEGIN
    DECLARE @result DATE;
    DECLARE @casted VARCHAR(8);

	IF (@agent_date IS NULL or @agent_date = 0) BEGIN
        SET @result = NULL;
    END
    ELSE BEGIN
        SET @casted = CONVERT(VARCHAR(8), @agent_date);
        SET @result = DATEFROMPARTS(
            CONVERT(INT, SUBSTRING(@casted, 1, 4))
          , CONVERT(INT, SUBSTRING(@casted, 5, 2))
          , CONVERT(INT, SUBSTRING(@casted, 7, 2))
        );
    END;
      
    RETURN @result;  
END
GO


/*
Converts SQL Server Agent DateTime to DateTime
*/
CREATE FUNCTION dbo.fnc_agentDateTimeToDateTime (
	@agent_date INT
  , @agent_time INT
)
RETURNS SMALLDATETIME
AS
BEGIN
    DECLARE @result SMALLDATETIME;
    DECLARE @agent_date_casted DATE;
    DECLARE @agent_time_casted TIME(0);

	IF (@agent_date IS NULL OR @agent_time IS NULL) BEGIN
        SET @result = NULL;
    END
    ELSE BEGIN
        SET @agent_date_casted = dbo.fnc_agentDateToDate(@agent_date);
        SET @agent_time_casted = dbo.fnc_agentTimeToTime(@agent_time);
        SET @result = DATETIME2FROMPARTS (
            DATEPART(YYYY, @agent_date_casted)
          , DATEPART(MM, @agent_date_casted)
          , DATEPART(DD, @agent_date_casted)
          , DATEPART(HH, @agent_time_casted)
          , DATEPART(MI, @agent_time_casted)
          , DATEPART(SS, @agent_time_casted)
          , 0
          , 0
        )
    END;
      
    RETURN @result;  
END
GO