CODEWITHSUNDEEP
sqlstored-procedures
Nick Kahn
Nick Kahn

Reputation: 20078

SQL statement between date

This is driving me crazy and not sure what I'm missing here..

so here is my data column looks like:

StartDateTime:
---------------
2012-01-17 11:13:46.530
2012-01-17 11:17:22.530
2012-02-17 11:31:22.223

here is my query trying to get:

select * from tablName
where convert(varchar(10), startDateTime, 101) between '2012-01-17' and '2012-01-17'

based on the above I should be getting TWO rows? but it does not, it return zero rows. what will be the correct way of doing?

PS: I've looked at the MSDN site too:

Upvotes: 1

Views: 1919

Answers (4)

Tahbaza
Tahbaza

Reputation: 9548

There is now more information so I'll add a more appropriate answer. The requirements are now a stored procedure passed a Date type parameter, not DateTime, and the desire is to return rows from a table based on criterion against a DateTime field named StartDateTime...

create procedure dbo.spGetEntriesForOneDay
@DesiredDate DateTime
as
SET NOCOUNT ON;
SET @DesiredDate = DATEADD(day, DATEDIFF(day, 0, @DesiredDate), 0)

SELECT Field1, Field2 -- see note 1
FROM dbo.TableName
WHERE StartDateTime >= @DesiredDate -- see note 2 
  AND StartDateTime < DATEADD(day, 1, @DesiredDate)  -- see note 3

NOTE 1: Don't use * in production code, especially in a stored procedure. Besides being wasteful by returning columns you probably don't need and precluding the optimization of a covering index on a subset of the columns required you would need to recompile this stored procedure whenever the underlying table is altered in order to avoid unpredictable results.

NOTE 2: Avoid wrapping fields in functions. A field not wrapped in a function can potentially be matched by the optimizer to an index while a field wrapped in a function never will.

NOTE 3: @Martin Smith and @RedFilter are correct in that .997 precision assumes DateTime datatype forever; this approach is more future proof because is makes no assumptions of data type precision.

Upvotes: 1

Dave Jorgenson
Dave Jorgenson

Reputation: 56

You can use the DateDiff function in the where clause. It would look like this:

select col1, col2 from tablName where DateDiff(day, startDateTime, @DesiredDate) = 0

Upvotes: 0

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171351

Your query would only match dates that are between 2012-01-17 00:00:00 and 2012-01-17 00:00:00. So, the only matches would be when the date is exactly 2012-01-17 00:00:00.

Instead, I would do this:

declare @dateInput as DateTime
set @dateInput = '2012-01-17'

select * 
from tablName 
where startDateTime >= @dateInput
    and startDateTime < dateadd(day, 1, @dateInput)

Note: SQL Server 2008+ has a new data type Date with no time component that can make these types of queries more readable.

Upvotes: 3

Tahbaza
Tahbaza

Reputation: 9548

You're using a datetime field (I'm guessing). Don't forget the time:

select * from tablName
where startDateTime between '2012-01-17' and '2012-01-17 23:59:59.997'

Upvotes: 0

Related Questions