I am saving files in SQL Server 2005 with a datetime field called news_date_time and I want to display all today's records regardless of the record time.
I tried this code but didn't work..
[code]
SqlCommand sql_command = new SqlCommand("SELECT * FROM files_news WHERE news_date_time = TODAY ORDER BY news_date_time DESC", sql_connection);
[/code]
select * from orders
where convert(datetime,floor(CONVERT(FLOAT,orderdate)))= --<-- susbstitute the columns to qualify
convert(datetime,floor(CONVERT(FLOAT,GETDATE())))|||? The only problem with that method is that it's quite bad for performance. An index on "orderdate" will not be usable by the query engine to help satisfy the query. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <joeydj@.discussions.microsoft.com> wrote in message news:d496ce31-0a56-41f7-902f-bcd35c0ab29f@.discussions.microsoft.com...use northwindselect * from orderswhere convert(datetime,floor(CONVERT(FLOAT,orderdate)))= --<-- susbstitute the columns to qualifyconvert(datetime,floor(CONVERT(FLOAT,GETDATE())))|||Conversion will lead to bad execution plans or even table scans. You should always use the built-in functionality of the appropiate data types to achieve your goals and the best queryplan.
HTH, Jens SUessmeyer.
http://www.sqlserver2005.de
|||
i see.
thanks
|||SELECT*FROM Employees
WHERECONVERT(varchar(15), hiredate, 112)=CONVERT(varchar(15),GETDATE(), 112)
Adamus
|||? Once again: Do not use that if you care about performance! Assuming that an index exists on "hiredate" in your example, SQL Server will not be able to use it. Please see my previous reply to this thread for an example of how to correctly phrase the query so that it can use an index. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Adamus Turner@.discussions.microsoft.com> wrote in message news:00a1169a-7acd-4c96-b6db-b130024a5e77@.discussions.microsoft.com... SELECT * FROM Employees WHERE CONVERT(varchar(15), hiredate, 112) = CONVERT(varchar(15), GETDATE(), 112) Adamus|||There's a performance issue converting a date into a string and then comparing a string?
Can this performance difference even be measured?
Adamus
|||? No, the performance issue is that SQL Server doesn't have an index that helps it find the value of the column when converted to a string; it has an index on the actual date value. When you convert it to a string, that index is no longer usable -- so instead of being able to seek against the index to find the rows you want, SQL Server has to scan every row of the table, one-by-one, converting each of the hiredate values into a string, then comparing that string. Might not be too bad for a few hundred or even a few thousand rows, but imagine it having to do that for millions of rows. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Adamus Turner@.discussions.microsoft.com> wrote in message news:d59ca8af-b2cc-46f5-9d3f-24f113d9bb01@.discussions.microsoft.com... There's a performance issue converting a date into a string and then comparing a string? Can this performance difference even be measured? Adamus|||Well then you need to contact MS to inform them and all SQL users that an index is removed from a field where a conversion takes place.NNTP User wrote:
? No, the performance issue is that SQL Server doesn't have an index that helps it find the value of the column when converted to a string; it has an index on the actual date value. When you convert it to a string, that index is no longer usable -- so instead of being able to seek against the index to find the rows you want, SQL Server has to scan every row of the table, one-by-one, converting each of the hiredate values into a string, then comparing that string. Might not be too bad for a few hundred or even a few thousand rows, but imagine it having to do that for millions of rows.
--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html?bID=457
--<Adamus Turner@.discussions.microsoft.com> wrote in message news:d59ca8af-b2cc-46f5-9d3f-24f113d9bb01@.discussions.microsoft.com...
There's a performance issue converting a date into a string and then comparing a string?
Can this performance difference even be measured?
Adamus
Good luck,
Adamus
|||Yes. See the last few messages in this thread: http://groups.google.com/groups/search?q=%22function+to+return+today+at+midnight%22 And see http://groups.google.com/groups/search?q=strik+kass+%22small+improvement%22 Steve Kass Drew University www.stevekass.com Adamus Turner@.discussions.microsoft.com wrote:
> There's a performance issue converting a date into a string and then
> comparing a string?
>
> Can this performance difference even be measured?
>
> Adamus
>
>
No comments:
Post a Comment