April 4, 2012 at 5:09 am
i have a field in my table ,
its type is DateTime, now i want to write a storeprocedure
for comparing this field with a parameter wich have only date
,i mean as you know my filed has date and time but
my parameter has only date forexample
my field:2012-03-27 19:00:00.000
but my parameter :2012-03-27
would you please help me?
April 4, 2012 at 5:16 am
You can cast both to DATE type:
SELECT CASE
WHEN CAST(@datetimefield AS DATE) = CAST(@dateonlyparameter AS DATE)
THEN 'Same date'
ELSE 'Different date'
END
-- Gianluca Sartori
April 4, 2012 at 8:16 am
Gianluca Sartori (4/4/2012)
You can cast both to DATE type:
SELECT CASE
WHEN CAST(@datetimefield AS DATE) = CAST(@dateonlyparameter AS DATE)
THEN 'Same date'
ELSE 'Different date'
END
could also do
SELECT CASE
WHEN DATEADD(dd,0,DATEDIFF(dd,0,@datetimefield)) = DATEADD(dd,0,DATEDIFF(dd,0,@dateonlyparameter))
THEN 'Same date'
ELSE 'Different date'
END
i would test to see which is faster.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 4, 2012 at 9:28 am
capn.hector (4/4/2012)
i would test to see which is faster.
I know what you're thinking, it's far faster to not cast a datetime as a string so it follows that it'll be faster to not cast as a date.
Actually, this is not the case. Don't run the below code on a production server, during the code I clear your cache which could cause all sorts of issues on a production database.
SET NOCOUNT ON;
--Standard TestEnvironment of 1,000,000 rows of random-ish data
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
--random datetimes between '2012-01-01 00:00:00 and 2012-12-31 23:59:59
RAND(CHECKSUM(NEWID())) * 366 + CAST('2012' AS DATETIME) AS randomDate
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
CREATE CLUSTERED INDEX idx_clu_testEnvironment_ID ON #testEnvironment (ID);
--Holder to take the display time out of the equation
DECLARE @HOLDER BIT;
--Variable used by the SPROCs
DECLARE @Date DATETIME = '2012-03-27';
PRINT REPLICATE('-',80);
PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';
PRINT REPLICATE('-',80);
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
PRINT REPLICATE('-',80);
PRINT 'CAST AS DATE'
PRINT REPLICATE('-',80);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = CASE WHEN CAST(randomDate AS DATE) = CAST(@Date AS DATE)
THEN 1 ELSE 0 END
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('-',80);
PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';
PRINT REPLICATE('-',80);
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
PRINT REPLICATE('-',80);
PRINT 'TRADITIONAL DATEDIFF/DATEADD'
PRINT REPLICATE('-',80);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = CASE WHEN DATEADD(dd,0,DATEDIFF(dd,0,randomDate)) = DATEADD(dd,0,DATEDIFF(dd,0,@Date))
THEN 1 ELSE 0 END
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
--------------------------------------------------------------------------------
CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE
--------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--------------------------------------------------------------------------------
CAST AS DATE
--------------------------------------------------------------------------------
Table '#testEnvironment'. Scan count 1, logical reads 2608, physical reads 1, read-ahead reads 2388, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 343 ms, elapsed time = 373 ms.
--------------------------------------------------------------------------------
CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE
--------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--------------------------------------------------------------------------------
TRADITIONAL DATEDIFF/DATEADD
--------------------------------------------------------------------------------
Table '#testEnvironment'. Scan count 1, logical reads 2608, physical reads 1, read-ahead reads 2388, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 577 ms, elapsed time = 589 ms.
The results are fairly consistent, cast as a DATE is faster than the more traditional method.
April 4, 2012 at 9:35 am
Cadavre (4/4/2012)
capn.hector (4/4/2012)
i would test to see which is faster.I know what you're thinking, it's far faster to not cast a datetime as a string so it follows that it'll be faster to not cast as a date.
Actually, this is not the case. Don't run the below code on a production server, during the code I clear your cache which could cause all sorts of issues on a production database.
SET NOCOUNT ON;--Standard TestEnvironment of 1,000,000 rows of random-ish data
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
--random datetimes between '2012-01-01 00:00:00 and 2012-12-31 23:59:59
RAND(CHECKSUM(NEWID())) * 366 + CAST('2012' AS DATETIME) AS randomDate
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
CREATE CLUSTERED INDEX idx_clu_testEnvironment_ID ON #testEnvironment (ID);
--Holder to take the display time out of the equation
DECLARE @HOLDER BIT;
--Variable used by the SPROCs
DECLARE @Date DATETIME = '2012-03-27';
PRINT REPLICATE('-',80);
PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';
PRINT REPLICATE('-',80);
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
PRINT REPLICATE('-',80);
PRINT 'CAST AS DATE'
PRINT REPLICATE('-',80);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = CASE WHEN CAST(randomDate AS DATE) = CAST(@Date AS DATE)
THEN 1 ELSE 0 END
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('-',80);
PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';
PRINT REPLICATE('-',80);
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
PRINT REPLICATE('-',80);
PRINT 'TRADITIONAL DATEDIFF/DATEADD'
PRINT REPLICATE('-',80);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = CASE WHEN DATEADD(dd,0,DATEDIFF(dd,0,randomDate)) = DATEADD(dd,0,DATEDIFF(dd,0,@Date))
THEN 1 ELSE 0 END
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
--------------------------------------------------------------------------------CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE
--------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--------------------------------------------------------------------------------
CAST AS DATE
--------------------------------------------------------------------------------
Table '#testEnvironment'. Scan count 1, logical reads 2608, physical reads 1, read-ahead reads 2388, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 343 ms, elapsed time = 373 ms.
--------------------------------------------------------------------------------
CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE
--------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--------------------------------------------------------------------------------
TRADITIONAL DATEDIFF/DATEADD
--------------------------------------------------------------------------------
Table '#testEnvironment'. Scan count 1, logical reads 2608, physical reads 1, read-ahead reads 2388, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 577 ms, elapsed time = 589 ms.
The results are fairly consistent, cast as a DATE is faster than the more traditional method.
That's a nice test and the cast is what I was thinking. another reason to post that is if some one is working with SQL 05 or lower there is not a date data type. Might not matter to the I and since I work with 08 I have some testing to do on several queries that were brought over from 05
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 4, 2012 at 9:40 am
Quick question, why the CAST(@DateOnlyVariable as DATE) when @DateOnlyVariable is already declared as a DATE data type?
Observation, casting a DATETIME data type to a DATE datatype seems on the surface to be faster, but couldn't that be because they are similar data types? In SQL Server 2008 and later, if all you want to do is drop the time portion from a DATETIME data type, casting to DATE makes sense. However, many of the other date conversions people request (first of the month for example) are done faster using dateadd(datediff()) manipulations.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply