Convert integer date to datetime

We may sometimes have to convert integer date to datetime. In this blog I will be discussing creating both date and time from integer and came up with datetime column. Care should be taken while using this function because it depend on how you create your date key or how you converted your time to int originally.
This demonstration is based on yyyymmdd format for the bigint the represent date and hhMMss to represent time as int. Since CONCAT function doesn’t work in older SQL Server versions there are two function given below.

/*
 * Auther:      Biz Nigatu						  
 * Description: This function will convert dataKey (bigint) and 
                time (int) into DateTime values.													  
 * Sample Use : SELECT dbo.fn_ConvertIntDateIntTimeToDateTime 
                (20030702, 173959  );	                                                      
*/
 
CREATE FUNCTION [dbo].fn_ConvertIntDateIntTimeToDateTime(@D as BigInt, @T as Int)
RETURNS DATETIME
AS
BEGIN
	DECLARE @DT AS DATETIME;
 
	IF LEN(@D) = 8
	BEGIN
		SELECT @DT =CAST(
                                   --this will create the date part
				   CONCAT((LEFT(@D,4) + '-' + 
					SUBSTRING(CAST(@D AS VarChar(8)),5,2) + '-' + 
					RIGHT(@D,2)) ,' ',
                                   --this will create the time part
				   (@T / 10000) % 100  ,':',
				   (@T / 100) % 100  ,':',
				   (@T ) % 100 )
				AS DATETIME	);
		RETURN @DT;
	END	
	RETURN NULL		
END 
 
GO
 
--If you are on pre SQL Server 2012 versions use this function instead
CREATE FUNCTION [dbo].fn_ConvertIntDateIntTimeToDateTime(@D as BigInt, @T as Int)
RETURNS DATETIME
AS
BEGIN
	DECLARE @DT AS DATETIME;
 
	IF LEN(@D) = 8
	BEGIN
		SELECT @DT =CAST(
                                   --this will create the date part
				CAST((LEFT(@D,4) + '-' + 
					SUBSTRING(CAST(@D AS VarChar(8)),5,2) + '-' + 
					RIGHT(@D,2)) AS VARCHAR) +' '+
                                   --this will create the time part
				   CAST((@T / 10000) % 100 as VARCHAR) +':'+
				   CAST((@T / 100) % 100 as VARCHAR) +':'+
				   CAST((@T ) % 100 as VARCHAR)
				AS DATETIME	);
		RETURN @DT;
	END	
	RETURN NULL		
END 
 
GO

Let us take an example to check our function is working