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 |