Convert an Integer Date to a Smalldatetime

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

1 Comment »

Comment by Tom De Cort
2007-12-21 17:28:40

Code below could also do the trick:

RETURN CAST(CAST(@yyyymmdd AS Varchar) AS DateTime)

Complete code of the function (with time parameter):


CREATE FUNCTION [dbo].[F_Convert_int_to_datetime]
(
–parameters for the function
@yyyymmdd int,
@hhmmss int
)
RETURNS DateTime
AS
BEGIN
— Calculate and return the result of the function
IF ISDATE(@yyyymmdd) = 1
BEGIN
RETURN CAST(
CAST(@yyyymmdd as varchar) + ‘ ‘ +
CAST(@hhmmss/10000 as varchar) + ‘:’ +
CAST((@hhmmss/100)%100 as varchar) + ‘:’ +
CAST((@hhmmss%100) as varchar)
as datetime)
END
— ELSE:
RETURN NULL
END

Greets
-Junior Tom

 
Name (required)
E-mail (required - never shown publicly)
URI
Your Comment (smaller size | larger size)
You may use <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> in your comment.

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word

Trackback responses to this post