All about Business Intelligence

A common way to represent a date - often in mainframe systems - is in the format yyyymmdd. Unfortunately it is not easy to do calculations on such a representation. To be able to use the native T-SQL date functions such as datediff and dateadd you will need to convert it to the datetime or smalldatetime type.

Convert the int 20071221 to the smalldatetime 2007-12-21

Here is a function that does exactly that:

CREATE FUNCTION IntDate2Smalldatetime
(
 @MFDate int
)
RETURNS smalldatetime
AS
BEGIN
 RETURN convert(smalldatetime, convert(char(4), @MFDate/10000)
           + '-' + convert(char(2), @MFDate/100 - (@MFDate/10000)*100)
           + '-' + convert(char(2), @MFDate - (@MFDate/100)*100))
END

Here is how to use it:

declare @id as int
set @id=20071221

/* convert int to smalldatetime */
select dbo.IntDate2Smalldatetime(@id)
  -- output: 2007-12-21 00:00:00

/* add 11 days to 20071221 */
select dateadd(d, 11, dbo.IntDate2Smalldatetime(@id))
  -- output: 2008-01-01 00:00:00

Convert the smalldatetime 2007-12-21 to the int 20071221

Stupid me! ‘yyyymmdd’ is one of the basic formats understood by SQLServer (2005, maybe prior versions too?).
Just convert the int to char or varchar and then to datetime. Kindly pointed out in the first comment below…

Converting in the other direction can be done with the following function:

CREATE FUNCTION Smalldatedime2IntDate
(
	@d smalldatetime
)
RETURNS int
AS
BEGIN
	return year(@d)*10000 + month(@d)*100 + day(@d)
END

Here is how to use it:

declare @dd as smalldatetime
set @dd={d '2007-01-01'}

/* convert smalldatetime to int */
select dbo.Smalldatedime2IntDate(@dd)
  --output: 20070101