Re: between dates with no time consideration

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 04/23/04 

Date: Fri, 23 Apr 2004 12:37:19 -0400

You could just add one day to @end if @start = @end. Specifically, if you
want the range to be from the beginning of the day on @start and the end of
the day on @end, then go with:

if @date >= @start and @date <= @end + 1
 select 1
else
 select 0 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Brian Henry" <brianiupmsdn@newsgroups.nospam> wrote in message
news:OGIAH9UKEHA.1272@tk2msftngp13.phx.gbl...
Example of the problem, i think i accidently left out that the date i am
compering this too has a time in it. just the range doesnt
declare @start as datetime
declare @end as datetime
declare @date as datetime
set @start = '4/23/2004'
set @end = '4/23/2004'
set @date = '4/23/2004 1:23PM'
if @date >= @start and @date <= @end
 select 1
else
 select 0
obviously that doesn't work, but I want to know if the @date is between the
start and end only by the date not the time. your example works if time isnt
a factor in the @date being compared :) sorry about leaveing out the one has
time part.
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:u3gF7jUKEHA.3292@TK2MSFTNGP11.phx.gbl...
> Let's say you have a proc like:
>
> create proc MyProc @date datetime
> as
> ...
>
> Now, you call it:
>
> exec MyProc '2004-04-23'
>
> The string gets implicitly converted to a datetime and the time component
> implicitly becomes midnight.
>
> The same is true when the proc is being executed via ADO from a client
APP.
>
> -- 
> Tom
>
> ---------------------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
>
> "Brian Henry" <brianiupmsdn@newsgroups.nospam> wrote in message
> news:OJpYqMUKEHA.1036@TK2MSFTNGP10.phx.gbl...
> Hi tom,
>
> Here's what I am doing maybe you can figure it out. what you stated I
> already understand.
>
> I am passing dates into a stored procedure, which since there is no single
> date data type, only datetime I must pass them through as a datetime...
How
> would I be able to used thoes parameters passed in as just a date like you
> stated? The dates are comming from a visual basic datetime picker with is
> only passing the date value back to the stored procedure, I'm assumeing
> between there and the stored procedure it adds a default time to it also.
>
>
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:OPa1tFUKEHA.1192@TK2MSFTNGP11.phx.gbl...
> > If you don't specify a time portion in your criterion, it will assume
> > midnight - i.e. the beginning of the day.  In your case, you would need
to
> > use:
> >
> > if @date >= '2004/04/23' and @date < '2004/04/24'
> >
> > -- 
> > Tom
> >
> > ---------------------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com/sql
> >
> >
> > "Brian Henry" <brianiupmsdn@newsgroups.nospam> wrote in message
> > news:ekxktAUKEHA.3924@tk2msftngp13.phx.gbl...
> > How would you say is this date between these two dates with out time
being
> > factored in?
> >
> > like say the date your looking for is 4/23/2004
> >
> > and you asking is it in the range of the days 4/23/2004 to 4/23/2004,
well
> > it should be because that is a date range of one day
> >
> > I thought it would work saying
> >
> > if @date >= @startDate and @date <= @endDate
> >
> > when logicaly that should work, the date is equal to the start and end
> > dates. but it doesn't work in my query, it will only work if the
startdate
> > is a day before the date... I think time is a problem here, what do you
> guys
> > thing?
> >
> >
>
>