SQL Query Help: WHERE ([fielddate]) = (GETDATE()) 2

[SW2004]

Hi,

I'm trying to find all faults in my helpdesk database that have been completed today. The query below doesn't return anything even though there are completed faults today. Can anyone help? Thanks.

SELECT [date fault reported], [date fault completed], [fault number], [status] FROM [fault table]
WHERE ([date fault completed]) = (GETDATE())

 

[checkai]

i always use this...

where convert(char(10),datefield,101) = convert(char(10),getdate(),101)

 

[vongrunt]

Karl, can you add CONVERT(date) to axis of evil (tm) list?

How about:

WHERE DATEDIFF(dd, [date fault completed], GETDATE()) = 0


-----
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[donutman]

First I'll put it on the Watch List and elevate the SQL Alerts to Code Yellow. Should sufficient evidence of DOS attacks against SQL Servers surface, it will indeed be added to the Axis whereupon you will be immediately notified. However, it may have to wait until I can get this to run.

[Blue]SELECT[/Blue] [Fuchsia]Convert[/Fuchsia][Gray]([/Gray]PhoneNumber[Gray],[/Gray][red]'0800-[(10x)(13i)^2]-[sin(xy)/2.362x]'[/red][Gray],[/Gray][Fuchsia]Pi[/Fuchsia][Gray])[/Gray]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SW2004]

Many thanks for the help.

 

[JamesLean]

FYI, using a function to search for a certain day's data will mean that any indexes created over that date column cannot be utilised. If your tables start getting large then the performance of the query may start to decline.

A better idea may be to use the following query, which will allow an index to be used if necessary:

SELECT [date fault reported], [date fault completed], [fault number], [status]
FROM [fault table]
WHERE [date fault completed] >= CONVERT(varchar(8), GETDATE(), 112)
	AND [date fault completed] < CONVERT(varchar(8), GETDATE() + 1, 112)

--James

 

[vongrunt]

Good point. And good query - CONVERT() only over GETDATE(), used ISO format (112) etc.

I thought SQL Server converts function calls into range retrieval operators (from-to) when possible.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[EdwinGene]

vg,

I know why cursors, "in (...)", and goto's are part of the Axis of Evil. Just to expand my own knowledge, why does Convert() deserve to be a member of the Axis of Evil? Is it because it is slow? And, is the related function Cast() also a candidate for the Axis of Evil?

 

[donutman]

It doesn't. We were just kidding...for the most part. However, the date math functions are faster and the only more efficient use of Convert that I can think of (aside from formatting) is to convert seconds, minutes, etc. into hh:mm:ss.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dbomrrsm]

Karl

should my link to the 150 functions be a candidate for the Axis of Evil ?

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[donutman]

Definitely!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

Hey! I found INC() very very useful

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[EdwinGene]

vg,

Don't know if you were responding to my post, but what I meant was "in (select...)". Instead of that I use "Exists (select...)" with a correlated subquery (much faster). "in (select...)" should be in the axis of evil.

 

[vongrunt]

No... that was reply to "150 functions" thing... I'm joking too much these days :X

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[EdwinGene]

No prob. But I do think "in (select...)" is a candidate for the Axis of Evil.

 

[donutman]

Vongrunt, didn't you demonstrate that IN (Select...) can be efficient? I think it was that long thread that I wanted you to turn into a FAQ. It's now lost forever in the bit bucket!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dbomrrsm]

Only just got to INC() - got to be the most useful function of all time - bet you it is as fast as hell too !!

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]