SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 convert system date into int format 'YYYYMMDD'
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

veparala
Starting Member

30 Posts

Posted - 02/15/2008 :  12:03:21  Show Profile  Reply with Quote
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?

Qualis
Posting Yak Master

USA
145 Posts

Posted - 02/15/2008 :  12:39:47  Show Profile  Reply with Quote
Try this:

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


You could also use:

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)

However, the performance is statistically identical:

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

Edited by - Qualis on 02/15/2008 12:40:11
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/15/2008 :  13:36:09  Show Profile  Reply with Quote

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)



CODO ERGO SUM
Go to Top of Page

veparala
Starting Member

30 Posts

Posted - 02/15/2008 :  13:45:50  Show Profile  Reply with Quote
Hi Qualis

Thanks for your reply. The following function is giving syntax error. Can you tell me how to resolve this?

CREATE FUNCTION plateflow.GetIntFromDate(@DateIn datetime)
RETURNS INT AS
Returns Int As Begin
Return Cast(Replace(Convert(varchar(10), DateIn, 120), '-', '') As int)
End
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/15/2008 :  14:01:33  Show Profile  Reply with Quote
Looks like the method I posted is the fastest, and the shortest to code.


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)



CODO ERGO SUM
Go to Top of Page

veparala
Starting Member

30 Posts

Posted - 02/15/2008 :  14:06:26  Show Profile  Reply with Quote
Thanks. It is working.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 02/18/2008 :  03:17:37  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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?


Where do you want to show converted dates?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 02/18/2008 :  04:18:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
When where Style 112 deprecated?

SELECT CONVERT(CHAR(8), GETDATE(), 112)



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 02/18/2008 :  04:22:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
CREATE FUNCTION dbo.fnDT2INT
(
	@DT DATETIME
)
RETURNS INT
AS
BEGIN
	RETURN CONVERT(CHAR(8), @DT, 112)
END
Timings
ET1 - 00:00:01.657
ET2 - 00:00:03.407
ET3 - 00:00:10.810
ET4 - 00:00:01.970



E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 02/18/2008 04:45:22
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000