current date Record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

current date Record

Author

Topic

esambath
Yak Posting Veteran

89 Posts

Posted - 2008-12-04 : 01:00:24

Hi Friends,

I have one Table name Report

Id
AgentName
Invoice
Amount
ndate

I want to display the Today Record only

this is my query
select * from Report where ndate=getdate()

Kindly please help this one

Regards
E.sambath

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-04 : 01:26:33

The easy way is

SELECT * FROM Report WHERE DATEDIFF(DAY, nDate, GETDATE()) = 0

The fast way is

SELECT * FROM Report
WHERE nDate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND nDate < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)

E 12°55'05.63"
N 56°04'39.26"

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-04 : 08:53:48

I'd say a faster way would be:


DECLARE 
	@today DATETIME,
	@tomorrow DATETIME
	
SELECT 
	@today = CAST(CAST(GETDATE() AS FLOAT) AS INT),
	@tomorrow = CAST(CAST(GETDATE() AS FLOAT) AS INT) + 1
	
SELECT * FROM report WHERE ndate BETWEEN @today AND @tomorrow

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-12-04 : 12:39:17

quote:

Originally posted by jholovacs

I'd say a faster way would be:


DECLARE 
	@today DATETIME,
	@tomorrow DATETIME
	
SELECT 
	@today = CAST(CAST(GETDATE() AS FLOAT) AS INT),
	@tomorrow = CAST(CAST(GETDATE() AS FLOAT) AS INT) + 1
	
SELECT * FROM report WHERE ndate BETWEEN @today AND @tomorrow

Faster, no, perhaps the same speed. But, casting and implicit conversion is bad form. Also, that would also include records that fall on the next day at midnight because BETWEEN in inclusive.

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-04 : 13:57:21

quote:
Faster, no, perhaps the same speed. But, casting and implicit conversion is bad form. Also, that would also include records that fall on the next day at midnight because BETWEEN in inclusive.

I disagree with the same speed. Any time you place a nondeterministic function (like GETDATE) in a WHERE clause you usually force a re-evaluation for each iteration of a set-based operation.

The bad form argument for CASTing is semantics, I believe... and I think in this case you will find it runs rings around the date functions from a performance standpoint, especially as it is outside the query proper. As it is only making 2 calculations, and the conversions are all numeric to begin with, the difference here is negligible, as long as the calculations are performed outside the query.

As for the the inclusiveness of the BETWEEN clause, no question, this has the potential of reporting 3 milliseconds more worth of range... my thought process was that reports generally report on past events, however, not future ones, although I suppose this report could be a projection analysis of some kind. Ultimately, a BETWEEN clause is parsed as a <= and a >= boundary operation, but you are correct, it does have the potential to produce a different result set in certain cases. I submit, then, this for your review:


DECLARE 
	@today DATETIME,
	@tomorrow DATETIME;
	
SELECT 
	@today = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0),
	@tomorrow = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1);
	
SELECT * FROM report WHERE ndate >= @today AND ndate < @tomorrow;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-04 : 15:05:58

GETDATE() is only evaluated once for a statement.

E 12°55'05.63"
N 56°04'39.26"

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-04 : 16:11:08

I had heard that before, but my independent testing in the past did not seem to bear it out. I did find some examples where it would appear to not be the case, but it also appears that this was a bug identified by MS at some point and has since been corrected:

http://sqlblog.com/blogs/andrew_kelly/archive/2008/02/27/when-getdate-is-not-a-constant.aspx

It looks like I will have to surrender the point and acknowledge that performance would be comparable... although I think my response was prettier...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources