Create Function dbo.GetIntFromDate(@DateIn datetime)
Returns Int As Begin
	Return Cast(Replace(Convert(varchar(10), DateIn, 120), '-', '') As int)
End

Cast(Cast(Year(DateIn) As varchar(4)) + 
	Right('0' + Cast(Month(DateIn) as varchar(2)), 2) +
	Right('0' + Cast(Day(DateIn) as varchar(2)), 2) as Int)

--Generate Test Data
Drop Table #table
Create Table #table (DateIn datetime)
Declare @cnt int
Set @cnt = 0
While @cnt < 1000000 Begin
	Insert #table Select GetDate() + @cnt
	Set @cnt = @cnt + 1
End

--Method 1
Select Cast(Replace(Convert(varchar(10), DateIn, 120), '-', '') As int)
From #table

/*
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.

(1000000 row(s) affected)
Table '#table'. Scan count 1, logical reads 2208, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, 
lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 2141 ms,  elapsed time = 8523 ms.
*/


--Method 2
Select Cast(Cast(Year(DateIn) As varchar(4)) + 
	Right('0' + Cast(Month(DateIn) as varchar(2)), 2) +
	Right('0' + Cast(Day(DateIn) as varchar(2)), 2) as Int)
From #table

/*
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.

(1000000 row(s) affected)
Table '#table'. Scan count 1, logical reads 2208, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, 
lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 1875 ms,  elapsed time = 8453 ms.
*/

select 
	Date_Int = year(DT)*10000+month(dt)*100+day(dt)
from
	-- Test Data
	(select dt=getdate()) a

Results:

Date_Int    
----------- 
20080215

(1 row(s) affected)

drop table #t

-- Load test data
select 
	DT = dateadd(dd,NUMBER,'17530101')
into
	#t
from

	F_TABLE_NUMBER_RANGE(0,3012153)
order by
	NUMBER

declare @st datetime
declare @Date_Int int

set @st = getdate()

select 
	@Date_Int = year(DT)*10000+month(DT)*100+day(DT)
from
	#t a

select ET_1 = right(convert(varchar(30),getdate()-@st,121),12)

set @st = getdate()

Select
	@Date_Int =
	Cast(Cast(Year(DT) As varchar(4)) + 
	Right('0' + Cast(Month(DT) as varchar(2)), 2) +
	Right('0' + Cast(Day(DT) as varchar(2)), 2) as Int)
from
	#t a

select ET_2 = right(convert(varchar(30),getdate()-@st,121),12)


set @st = getdate()

Select
	@Date_Int = Cast(Replace(Convert(varchar(10),DT, 120), '-', '') As int)
from
	#t a

select ET_3 = right(convert(varchar(30),getdate()-@st,121),12)


Test Results:

ET_1         
------------ 
00:00:04.017

(1 row(s) affected)

ET_2         
------------ 
00:00:09.173

(1 row(s) affected)

ET_3         
------------ 
00:00:20.420

(1 row(s) affected)

quote:

---

Originally posted by veparala

Hi

I need to write user defined function in sql server for the following. I need to convert system date into int format 'YYYYMMDD'.

Can anybody give that function?

---

CREATE FUNCTION dbo.fnDT2INT
(
	@DT DATETIME
)
RETURNS INT
AS
BEGIN
	RETURN CONVERT(CHAR(8), @DT, 112)
END

ET1 - 00:00:01.657
ET2 - 00:00:03.407
ET3 - 00:00:10.810
ET4 - 00:00:01.970