Where date = mydate 1

[chispavip]

I think this is an easy question.

In my table dates are in smalldatetime type, also it includes time values.

Like this
01/02/2006 11:30:00 p.m.


but when I construct the query like this
...
where Table.Date = myDate



my date only contains the date part (01/02/2006) and the result set is an empty set.


So how can I take just the date part in the table?

I mean, at this point I don't care the time value

 

[SQLDenis]

where Table.Date >= myDate
and < myDate +1

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[mrdenny]

There are two methods you can use.

select *
from table
where Table.DateField between @MyDate and dateadd(dd, 1, @MyDate)

Or

select *
from table
where convert(varchar(10), Table.DateField, 101) = convert(varchar(10), @MyDate, 101)

If the field has an index the first option will be your best bet. The second option should only be used on small tables or tables which don't have an index where you have to do a full table scan either way.

The first option will use any indexes which exist on the column. The second option will not and will force a table scan.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLDenis]

>>where Table.DateField between @MyDate and dateadd(dd, 1, @MyDate)


be carefull with between and dates
if you do where date between 20061204 and 20061205

it will also include the midnight value for the 20061205 date, read more here (

http://sqlservercode.blogspot.com/2006/10/do-you-know-how-between-works-with.html)

>>where convert(varchar(10), Table.DateField, 101) = convert(varchar(10), @MyDate, 101)


this will cause a table scan and should not be used
see here for an explanation (

http://sqlservercode.blogspot.com/2005/12/sql-query-optimizations.html)

>= and < are the only optimized way to do this query

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLDenis]

Of course I didn't read the whole response that mrdenny posted before posting myself ;-(

so my second comment is bogus since he already said the same thing basically

However between still can cause a problem if you have dates that fall on midnight since between is inclusive

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLBill]

I'd also like to point you to the FAQs for this forum. There is a section on Datetime Tips & Tricks. It's pretty handy knowledge.

-SQLBill

Posting advice: FAQ481-4875

 

[chispavip]

thanks that works
I did it in this way
WHERE TDate BETWEEN @MyDate AND DATEADD(day, 1, @MyDate)


but there is still a problem.

Suppose that I have this on my table
R1 12/19/2006 13 hrs
R2 12/19/2006 15 hrs
R3 12/19/2006 24 hrs
R4 12/20/2006 0 hrs

it gets R4 too.

What I did was to add -1 minutes to mydate

WHERE TDate BETWEEN @MyDate AND DATEADD(minute, -1, DATEADD(day, 1, @MyDate))


It is a solution, but isn't another way or easier way to do this?
I mean a way to compare just dates

 

[gmmastros]

As Denis pointed out (twice)...

[tt][blue]
WHERE TDate >= @MyDate
And TDate < @MyDate + 1
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

>> it gets R4 too.

that's what I said didn't I?

>>What I did was to add -1 minutes to mydate
what if you have a value that is 5 seconds less?

here is the preferred way

WHERE TDate >= @MyDate
AND TDate < @MyDate +1

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[mrdenny]

If you need to subtract some time you should subtrack 1 milisecond (the smallest amount of time that SQL accepts) otherwise you can miss data.

My first code block should look like this.

select *
from table
where Table.DateField between @MyDate and dateadd(ms, -1, dateadd(dd, 1, @MyDate))

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com