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.
*/

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?

---