CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Steve Evans
Steve Evans

Reputation: 1148

SELECT WHERE Date = Day

I have a table with a column of type dateTime. I want to do a query that selects all rows that take place on that date. Basically,

SELECT * FROM Table
WHERE [timeStamp] = '02-15-2003'

But that only returns rows where [timeStamp] is '02-15-2003 00:00:00.000', but really I want rows from anytime that day.

Upvotes: 8

Views: 45224

Answers (7)

mattmc3
mattmc3

Reputation: 18355

You should CAST to DATE if you're on SQL 2008.

select * from [Table]
where cast([timeStamp] as date) = '02-15-2003'

Best approach to remove time part of datetime in SQL Server

--- UPDATE ---

The word the commenters should have used back in 2012 to describe why this is not the optimal solution is sargability. Changing the data type in the WHERE clause, while the simplest solution, has implications for index usage that a bounded search would not.

Upvotes: 9

Rusty Nail
Rusty Nail

Reputation: 2700

MS SQL 2014, this works perfect:

SELECT [YourDateColumn] FROM [YourTable] WHERE(DATEPART(dd,[YourDateColumn]) = '29')

Might have some performance issues on very large DB's.

Upvotes: 1

Internet Engineer
Internet Engineer

Reputation: 2534

I would create a stored procedure that will accept "start date" and "end date"

In this case the start date and end date can be the same

This ensures that all rows from 12:01 AM to 11:59 PM are returned

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE  TestBetweenDates
    -- Add the parameters for the stored procedure here
    @StartDate DateTime = 0, 
    @EndDate DateTime = 0
AS
BEGIN

    SET NOCOUNT ON;

        SET @StartDate = cast(Convert(varchar(10), DateAdd(d, -6, @StartDate ), 101) + ' 12:01 AM' as datetime)

        SET @EndDate = cast(Convert(varchar(10), DateAdd(d, -0, @EndDate), 101) + ' 11:59 PM' as datetime)

SELECT * FROM Table
WHERE ([timeStamp] BETWEEN @StartDate AND @EndDate) 


END
GO

Upvotes: 0

Cade Roux
Cade Roux

Reputation: 89741

If you have indexes, you are going to want something which doesn't prevent the indexes from being used:

SELECT *
FROM Table 
WHERE [timeStamp] >= '20030215'
      AND [timeStamp] < '20030216'

You can do a truncation operation on the [timeStamp] column to get rid of any time part (implementation dependent), but this can potentially hurt the execution plan. Unfortunately, you really have to look at the execution plan to see this, because sometimes the optimizer is clever about some functions and sometimes it isn't.

Upvotes: 19

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

try this.. SQL SERVER

SELECT * FROM Table WHERE convert(date,[timestamp]) = '2003-02-15'

should return all rows on the specified day.

Upvotes: 0

mson
mson

Reputation: 7822

Date comparisons can be a tricky thing.

Remember that it is a datetime and not a string. This is why you got unexpected results.

For the specific query you have in mind, the appropriate query is

SELECT * FROM Table 
WHERE 0 = datediff(day,[timestamp],'02-15-2003')

You may also do compares by month() and year() which return integer values.

You usually have to write custom functions to get comparisons that are non-trivial.

Also note

WHERE 0 = datediff(day,[timestamp],'02-15-2003')

is much better than

WHERE datediff(day,[timestamp],'02-15-2003') = 0

The former does not interfere with internal efficiency while the latter does.

Upvotes: -6

Royi Namir
Royi Namir

Reputation: 148744

  in sql server 2008 + :

  SELECT * FROM Table
        WHERE cast( [timeStamp] as date)  = '02-15-2003'

or

just ZERO the time part :  ( 2005+)

   SELECT * FROM Table
    WHERE DateAdd(day, DateDiff(day, 0, [timeStamp]), 0)  = '02-15-2003'

Upvotes: 1

Related Questions