Re: between dates with no time consideration
From: Brian Henry (brianiupmsdn_at_newsgroups.nospam)
Date: 04/23/04
- Next message: Mike: "SQL Join or Subquery?"
- Previous message: Steve Kass: "Re: Found a bug - is it known? can others reproduce it?"
- In reply to: Tom Moreau: "Re: between dates with no time consideration"
- Next in thread: Alejandro Mesa: "Re: between dates with no time consideration"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 23 Apr 2004 13:41:15 -0400
thanks that fixed it
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:%23wdHKFVKEHA.1132@TK2MSFTNGP12.phx.gbl...
> 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?
> > >
> > >
> >
> >
>
>
- Next message: Mike: "SQL Join or Subquery?"
- Previous message: Steve Kass: "Re: Found a bug - is it known? can others reproduce it?"
- In reply to: Tom Moreau: "Re: between dates with no time consideration"
- Next in thread: Alejandro Mesa: "Re: between dates with no time consideration"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
