Between Operator

[selanec]

Between Dates e.g.

Blah blah WHERE MyDate Between @StartDate and @EndDate

Say i chose 11/5 to 11/25
I expect it to returns all records between the two dates but the time to be considered as well. Currently the query is check up until 11/25 12am omitting records written after 12am.

It should return all records from 11/5 12am THRU 11/25 11:59:59pm

Thanks

[si_the_geek]

A DateTime value without a time portion is considered to be midnight (12am).

While specifying @StartDate without a time allows all values from that day, the same does not apply to @EndDate - it will only allow values of exactly midnight.


There are two easy ways to deal with it, either add the time 11:59:59pm to @EndDate, or convert the field (MyDate) to just the date portion.

Exactly how you do those things depends on things like what database system and code you are using.

[selanec]

I am using SQL Server 2008 (T-SQL)

Would you be so kind and show me how do i add the time to @EndDate .. should i cast it from varchar or something?

Thank you so much

[techgnome]

WHAAAT? you're using a varchar to hold a date? Ugh... Would you treat your car like a horse? Of course not! So treat a date as a date should be treated... give it the proper respect and love it deserves.

And then add to it using the dateAdd function... http://msdn.microsoft.com/en-us/library/ms186819.aspx
Add a day... then subtract a second...

set @ENDDATE = dateadd(s, -1, dateadd(d, 1, @ENDDATE))

-tg

[selanec]

No no i am not using varchar to hold a date. I was rather referring to something like following (when you have to merge the date from strings e.g.

SET @MyDate = CAST(CAST(YEAR(GetDate()) AS varchar(4)) + '-' + CAST(MONTH(GetDate()) AS varchar(2)) + '-' + CAST(@MyDay AS varchar(2)) AS DATETIME)

Ok according your code i understand that you add a DAY and then subtract 1 sec. Actually it makes sense. Will give it a try. Thank you guys