Select Question using DateTime Field 1

[UncleCake]

Hi,

I am trying to write a select statement in a SP that returns records > or = to a start date and < or = to an end date. The time and date are stored in this field of SQL Server.

The problem is that it is not returning any records equal to the end date; It only returns from the start date to one day prior to the end date.

I assume that it is because it is looking for the end date with a 00:00:00 time, which would make it end the day before the end date.

How do I just select the records with a start and end date without using a time in the end date?

-Uncle Cake

 

[gmmastros]

You could strip the time off the field before doing the comparison.

You could also add 1 day to the end date before doing the comparison (or better yet 86399 seconds -- 11:59:59 PM).

-George

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

 

[Pattycake245]

Use convert function found in BOL. You can then chose a format that will not include the time:

select convert(char(10),getdate(),120)

returns 2005-08-09

Tim

 

[UncleCake]

Pattycake245,

I ended up working off of your answer. I did a convert on the WHERE part of the clause, which then only checked the date and not the time, so thank you for the start.

George,

I also thought about adding the day, but I ended up using a different method, but thanks for the help and the option.

-Justin