Jason Salas' WebLog

Don't know if this is faster or slower performance wise, but this was my first thought on this:

SELECT * FROM tablename
WHERE DATEPART(d,[Date]) = DATEPART(d,'7/7/2004')

Also, someone [ESquared] was nice enough to note the following after I posted my code:

• the first select statement will also return records from 7/8/2004 12 am because BETWEEN is inclusive. In any case you can just do @QueryDate + 1, no need to use DateAdd.

• In the second select, it is unnecessary to add +'00:00:00' because that is truly adding zero.

• smalldatetime does not store seconds. So, adding '23:59:59' ends up adding 24 hours and thus it is identical to the previous select. Change it to '23:59' for correct selection.

• the leading space character is unnecessary. The query engine is not adding a string to a string, it is adding a date to a date. Because of the way dates are stored internally in SQL server, '23:59' is equivalent to '1900-01-01 23:59', (and 23:59:59 is 24 hours) so you're really doing: '1900-01-02 00:00:00' + @QueryDate

This would be a revised version of the query:
SELECT * FROM TableName WHERE [Date] >= @QueryDate AND [Date] < @QueryDate + 1

Note the second comparison is not <=.

I've always written it like this:

SELECT * FROM Orders Where CAST(CONVERT(varchar,OrderDate,101) AS DateTime) = '7/7/2004'

Convert the column to 'mm/dd/yy' (101) style and CAST it back to a datetime.

Hi Lucky,

Someone advised me against that:

"the conversion must be done for every row in the entire table and to boot may not use any indexes. Selecting with the >= AND < method has neither of these problems."

As much as possible, avoid having computations on the column side of a comparison...shift the computation over to the "constant" side:

WHERE CAST(CONVERT(varchar,OrderDate,101) AS DateTime) = '7/7/2004' would be suboptimal and won't directly work for say, GETDATE(), as you must have the time stripped out

instead do:

DECLARE @Date datetime
SET @Date = FLOOR(CONVERT(float, GETDATE()))
..
WHERE OrderDate BETWEEN @Date AND @Date + 1

This will be more efficient and will take advantage of an index on OrderDate if one exists. @Date + 1 and FLOORing a date works in SQL Server 7 and 2000 and is faster than DATEADD and a CONVERT to a varchar with a style. It might produce different results (it might even be illegal) in future versions or other RDBMSs. I forgot if it worked in 6.5.

Where [date] like '7/1/2004%'

? Does that work, I think it should. Don't have Query Analyzer here in front of me to test it with.

Scoot - no:

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

match_expression
Is any valid SQL Server expression of character string data type.

a datetime is not a character string data type. And it will only work with US-English locale, which may be a problem as well.

And to comment on Jason's note: DATEDIFF will also use indexes. That seems to be the best way to search on dates belonging to one day - DATEDIFF(dd, @datetomatch, [date-column]) = 0. Matt's method of using DATEPART is flawed as it will match the same dates in different years.

Thanks all for your input. This is actually the code I used in the end to get what I wanted to do. I realize that the @Date + 1 might not be the fastest way to get things done, but in my code, wherein I have to return records for a successive number of days, it works:

CREATE PROCEDURE GetWeeklyStories
(
@QueryDate SMALLDATETIME
)
AS
DECLARE @incrementor INT
SET @incrementor = 1

-- get the first story for the latest day in the week
SELECT * FROM TableName WHERE a.Date >= @QueryDate AND a.Date < @QueryDate + 1;

-- now, get the remaining stories for the week, counting forward
WHILE @incrementor <= 6
BEGIN
SET @QueryDate = @QueryDate + 1
SELECT * FROM TableName WHERE a.Date >= @QueryDate AND a.Date < @QueryDate + 1;
SET @incrementor = @incrementor + 1
END
GO

Jason, use DATEDIFF. That's what's it there for. Adding numbers to dates may break at any moment, DATEDIFF is guaranteed to work.

although using DATEDIFF on a column will use a clustered index on that column if one exists, it will perform an index scan which can be very costly

using "BETWEEN @Date AND @Date + 1" and a clustered index on the concerned column will will perform an index seek, which is potentially much faster than an index scan

in the sample I have right now with 5000+ rows, DATEDIFF produces 65 logical reads while "BETWEEN @Date AND @Date + 1" produces only 2 logical reads

if you have a table with few rows this might make little difference but if you are dealing with tables in the hundreds of thousands or millions of rows then it will be a big deal

additionally, it seems both approaches wont use a non-clustered index so make sure your index is clustered. clustered indexes should be SOP on columns that heavily participate in range queries like this one

I have tried all of em nothing is work

Greeting. I wrote myself a check for ten million dollars for acting services rendered and dated it Thanksgiving 1995. I put it in my wallet and it deteriorated. And then, just before Thanksgiving 1995, I found out I was going to make ten million dollars for Dumb & Dumber. I put that check in the casket with my father because it was our dream together. Help me! I find sites on the topic: Dwell baby bedding. I found only this - <a href="baby-bedding.net/.../">velvet baby bedding</a>. Bedding, west, in both his father darwin day in america and also does, has treated to learn darwin to the channel counsel. Bedding, follow you out feel to collect your people, and create your business excavate once right and open that indeed your article will import you for scaling about skincare? Thank :mad: Anselme from Bolivia.

getting birth year from datetime field by sql querry in asp.net ?

I'll right away grab your rss as I can not find your e-mail subscription link or newsletter service. Do you've any' Please let me know so that I could subscribe. Thanks.

<b><a href="mybloggityblog.bloghitz.com/.../a-prestigious-lock-gathering-blank

">Home Security Monitoring

<a/><b/>