just the date, please

Question

Post reply

just the date, please

Viewing 15 posts - 16 through 30 (of 62 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 1

This has one caveat as MVJ has pointed out. It does not work for 1753-01-01

PRINT '===== Minus method ====='

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @MyDate=CAST(Adate - 0.50000004 as INT) FROM dbo.BigTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

===== Rounding method 2 ======

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

686 Milliseconds

===== DateDiff/DateAdd method ======

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

610 Milliseconds

===== Rounding method 1 ======

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

703 Milliseconds

===== Convert method ================

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

1266 Milliseconds

===== Johnathons's Integer function =====

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

623 Milliseconds

===== Floor method =====

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

626 Milliseconds

===== Minus method =====

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

606 Milliseconds

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

Jeff Moden SSC Guru Points: 1003907 More actions · Answer 2

Dang, Peter... you must be working on a really nice box... my poor ol' 1.8Ghz home computer SQL 2k Developer's Edition just didn't do that well... and your's does much better than mine on the ones that use Convert... what are you running?

===== Rounding method 2 ======

720 Milliseconds

===== DateDiff/DateAdd method ======

826 Milliseconds

===== Rounding method 1 ======

2970 Milliseconds

===== Convert method ================

5093 Milliseconds

===== Johnathons's Integer function =====

640 Milliseconds

===== Floor method =====

1813 Milliseconds

===== Minus method =====

783 Milliseconds

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 3

SQL Server 2008 Developer Edition

1 GB RAM (SQL Server service 256 MB)

Fujitsu Siemens E8020 LifeBook Laptop Pentium-M 2GHz

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

Jeff Moden SSC Guru Points: 1003907 More actions · Answer 4

I'm thinking that SQL Server 2k8 kicks some serious butt!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

(1000000 row(s) affected)

===== Rounding method 2 ======

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

373 Milliseconds

===== DateDiff/DateAdd method ======

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

390 Milliseconds

===== Rounding method 1 ======

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

643 Milliseconds

===== Convert method ================

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

1220 Milliseconds

===== Johnathons's Integer function =====

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

376 Milliseconds

===== Floor method =====

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

500 Milliseconds

SQL Server 2005 Developer Edition

3 GHz Intel Processor

2 GB RAM

I guess I should install the SQL Server 2008 CTP on this box and see how it performs.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 6

IBM x3950 with 16 Dual Core 3Ghz CPUs and 32Gb of memory with average, maximum and minimum durations in milliseconds from 10 executions:

SQL Server 2000:

Method	Average	Max	Min
Rounding method 2	353	360	343
Johnathons's Integer	366	376	356
DateDiff/DateAdd	390	390	390
Floor method	1078	1093	1076
Rounding method 1	1820	1830	1810
Convert method	3073	3093	3063

SQL Server 2005 on identical server:

Method	Average	Max	Min
Johnathons's Integer	352	373	343
Rounding method 2	369	390	360
DateDiff/DateAdd	442	483	420
Floor method	515	563	500
Rounding method 1	638	686	623
Convert method	1193	1300	1170

"Johnathons's Integer" and "Rounding method 2" methods are significantly faster (10 to 20 %) than the other methods:

Johnathon's Integer method can be re-written to use all CAST instead of convert with the average duration decreasing by 10%

CAST

( cast ( cast (Adate as float) as integer ) as datetime )

SQL Server 2005:

Method	Average	Max	Min
Johnathons's Revised	346	360	343
Johnathons's Integer	351	373	343

SQL = Scarcely Qualifies as a Language

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 7

Rounding method 2 has a desing flaw for all dates.

SELECT

DATEDIFF(DAY, 0, '20070708 23:59:59.997'),

CAST(cast('20070708 23:59:59.997' as datetime) - 0.50000004 as INT), -- minus method

CAST(cast('20070708 23:59:59.997' as datetime) - 0.50000000 as INT) -- rounding method 2

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

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 8

Accuracy test...

--===== Create a test table

CREATE

TABLE dbo.BigTest2

(

RowNum

INT NOT NULL,

theTime

DATETIME

)

SELECT

RowNum,

theTime

INTO

#Temp

FROM

dbo.BigTest2

DECLARE

@Loop INT

SET

@Loop = 0

WHILE

@Loop < 300

BEGIN

INSERT #Temp

(

RowNum

,

theTime

)

VALUES (

@Loop

,

DATEADD(ms, 3.3333333333333333333 * @Loop, 0)

)

SET @Loop = @Loop + 1

END

SET

@Loop = 1

WHILE

@Loop < 86400

BEGIN

INSERT dbo.BigTest2

(

RowNum

,

theTime

)

SELECT 300 * @Loop + RowNum,

DATEADD(second, @Loop, theTime)

FROM #Temp

SET @Loop = @Loop + 1

END

DROP

TABLE #Temp

--===== A table is not properly formed unless a Primary Key has been assigned

ALTER TABLE dbo.BigTest2

ADD PRIMARY KEY CLUSTERED (RowNum)

--===== Declare a couple of operating variables for the test

DECLARE

@Items INT,

@StartTime

DATETIME

PRINT '===== Rounding method 2 ======'

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @Items = COUNT(*) FROM dbo.BigTest2 WHERE CAST(theTime - 0.5 AS INT) <> 0.0

PRINT STR(@Items,10) + ' items'

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

PRINT '===== DateDiff/DateAdd method ======'

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @Items = COUNT(*) FROM dbo.BigTest2 WHERE DATEDIFF(dd, 0, theTime) <> 0.0

PRINT STR(@Items,10) + ' items'

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

PRINT '===== Rounding method 1 ======'

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @Items = COUNT(*) FROM dbo.BigTest2 WHERE ROUND(CAST(theTime AS FLOAT), 0, 1) <> 0.0

PRINT STR(@Items,10) + ' items'

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

PRINT '===== Convert method ================'

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @Items = COUNT(*) FROM dbo.BigTest2 WHERE CONVERT(CHAR(8),theTime,112) <> '19000101'

PRINT STR(@Items,10) + ' items'

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

PRINT '===== Johnathons''s Integer function ====='

-- Does NOT truncate... does a round be careful!!!!

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @Items = COUNT(*) FROM dbo.BigTest2 WHERE convert(int, convert(float, theTime)) <> 0.0

PRINT STR(@Items,10) + ' items'

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

PRINT '===== Floor method ====='

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @Items = COUNT(*) FROM dbo.BigTest2 WHERE FLOOR(convert(float, theTime)) <> 0.0

PRINT STR(@Items,10) + ' items'

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

PRINT '===== Minus method ====='

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @Items = COUNT(*) FROM dbo.BigTest2 WHERE CAST(theTime - 0.50000004 as INT) <> 0.0

PRINT STR(@Items,10) + ' items'

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

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

Jeff Moden SSC Guru Points: 1003907 More actions · Answer 9

Nicely done, Peter... not sure where my post went (must've done the timeout thing ), but I was going to say that your method and "Rounding Method 2" are virtually identical (your's relies on final implicit conversion to date/time) except that your's actually works correctly for "fringe" times in the 23:59:59.997 area

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 10

I personally use the DateDiff/DateAdd method which I picked up a while ago from one of the forums here, it performs adequately and performance has not been an issue for my purposes.

Regardless of which method you choose, I would suggest that you encapsulate the code in a UDF, mine looks like this:

CREATE FUNCTION [dbo].[fnNoTime]

(@DateTime datetime)

RETURNS datetime

AS

BEGIN

DECLARE @date datetime

SET @date = DATEADD(d,DATEDIFF(d,0,@DateTime),0)

RETURN @date

END

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Why add the overhead of the function call??? It's not like that this is a very complex condition that can't be explained without documentation.

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 12

Reusability, encapsulation, object orientation, refactoring, pre-baked code, whatever you want to call it. You establish a standard method and stick to it. Everybody on the team can use the same exact function, you're guaranteed the same outcome.

If you decide to update the methodology, simply update the function & it will automatically apply to everywhere the function is called.

Doesn't that make sense? It sure does to me.

Jeff Moden SSC Guru Points: 1003907 More actions · Answer 13

You may want to reconsider, Bill... or not "It Depends" ...

CREATE FUNCTION dbo.fnDateOnly

/***********************************************************

Purpose:

This function accepts anything that can be implicity

converted to a DATETIME datatype with or without a time

element and returns the date with a time of "Midnight".

***********************************************************/

(@DateTime DATETIME)

RETURNS DATETIME

AS

BEGIN

RETURN DATEADD(dd,DATEDIFF(dd,0,@DateTime),0)

END

GO

--===== Create a test table

SELECT TOP 1000000

IDENTITY(INT,1,1) AS RowNum,

CAST(CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS INT) AS DATETIME)+'23:59:59.997' AS ADate

INTO dbo.BigTest

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--===== A table is not properly formed unless a Primary Key has been assigned

ALTER TABLE dbo.BigTest

ADD PRIMARY KEY CLUSTERED (RowNum)

GO

--===== Declare a couple of operating variables for the test

DECLARE @MyDate DATETIME --Holds the result of a conversion to bypass display times

DECLARE @StartTime DATETIME --For measuring duration of each snippet

PRINT '===== DateDiff/DateAdd method ======'

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @MyDate=DATEADD(dd,DATEDIFF(dd,0,ADate),0) FROM dbo.BigTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

PRINT '===== DateDiff/DateAdd method as a Function ======'

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @MyDate=dbo.fnDateOnly(ADate) FROM dbo.BigTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 14

===== DateDiff/DateAdd method ======

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

653 Milliseconds

===== DateDiff/DateAdd method as a Function ======

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

3206 Milliseconds

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

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 15

I see your point Jeff, the function version takes a lot longer, by a 6:1 margin. In your example, on my 2.5 GHz Celeron ThinkPad it was 1.6 seconds vs. 11.2 seconds. But I rarely deal with a million rows, but if I do my customer's server has dual quad-core processors so that will cut the time diff way down.

Besides, if you're going to use that DATEADD(dd,DATEDIFF(dd,0,ADate),0) frequently, how the heck do you remember that exact syntax? This feature should have been built-in to SQL Server IMHO and I stil think that using a function will make the code more readable & maintainable, e.g.

dbo.fnNoTime(dtSomeDate) BETWEEN '1/1/2007' AND '1/31/2007'

vs

DATEADD(dd,DATEDIFF(dd,0,dtSomeDate),0) BETWEEN '1/1/2007' AND '1/31/2007'

MTCW