selecting dates in a date range
in [Programming SQL]
|
Prev: questions about merge...
Next: The number of columns for each row in a table value constructor must be the same.?
From: Andy B. on 24 Mar 2010 08:13 I have 2 columns in a table: HeadlineStartDate date not null, HeadlineEndDate date not null I need to do 3 things: 1. select all rows where the current date falls between HeadlineStartDate and HeadlineEndDate, 2. Select all rows where the current date is earlier than HeadlineStartDate and 3. Select all rows where the current date is later than HeadlineEndDate. How do you do this? they need to be all in different queries.
From: Uri Dimant on 24 Mar 2010 09:16 Andy declare @dt as date=getdate() 1) select * from tbl where HeadlineStartDate >= @dt and HeadlineEndDate<dateadd(d,1,@dt) 2) select * from tbl where HeadlineStartDate>dt 3)select * from tbl where HeadlineStartDate<dt "Andy B." <a_borka(a)sbcglobal.net> wrote in message news:u8a3gt0yKHA.3264(a)TK2MSFTNGP06.phx.gbl... >I have 2 columns in a table: > HeadlineStartDate date not null, > HeadlineEndDate date not null > > I need to do 3 things: > > 1. select all rows where the current date falls between HeadlineStartDate > and HeadlineEndDate, > 2. Select all rows where the current date is earlier than > HeadlineStartDate and > 3. Select all rows where the current date is later than HeadlineEndDate. > > How do you do this? they need to be all in different queries. >
From: Andy B. on 24 Mar 2010 10:13 "Uri Dimant" <urid(a)iscar.co.il> wrote in message news:u7nfiQ1yKHA.3264(a)TK2MSFTNGP06.phx.gbl... > declare @dt as date=getdate() > > 1) > > select * from tbl > > where HeadlineStartDate >= @dt and > > HeadlineEndDate<dateadd(d,1,@dt) > Wouldn't it be: where HeadlineStartDate < getdate() -- Make sure HeadlineStartDate is in the past and HeadlineEndDate > getdate() -- make sure HeadlineEndDate is in the future? These date comparisons have to be acurate to the minute. Will this method work?
From: Uri Dimant on 24 Mar 2010 10:21 Andy B You stated that a datatype is DATE for those values "Andy B." <a_borka(a)sbcglobal.net> wrote in message news:uWaKaw1yKHA.928(a)TK2MSFTNGP05.phx.gbl... > > "Uri Dimant" <urid(a)iscar.co.il> wrote in message > news:u7nfiQ1yKHA.3264(a)TK2MSFTNGP06.phx.gbl... >> declare @dt as date=getdate() >> >> 1) >> >> select * from tbl >> >> where HeadlineStartDate >= @dt and >> >> HeadlineEndDate<dateadd(d,1,@dt) >> > Wouldn't it be: > > where HeadlineStartDate < getdate() -- Make sure HeadlineStartDate is in > the past > and HeadlineEndDate > getdate() -- make sure HeadlineEndDate is in the > future? > > These date comparisons have to be acurate to the minute. Will this method > work? >
From: Andy B. on 24 Mar 2010 11:11
I did. "Uri Dimant" <urid(a)iscar.co.il> wrote in message news:eYVow01yKHA.3884(a)TK2MSFTNGP06.phx.gbl... > Andy B > > You stated that a datatype is DATE for those values > > "Andy B." <a_borka(a)sbcglobal.net> wrote in message > news:uWaKaw1yKHA.928(a)TK2MSFTNGP05.phx.gbl... >> >> "Uri Dimant" <urid(a)iscar.co.il> wrote in message >> news:u7nfiQ1yKHA.3264(a)TK2MSFTNGP06.phx.gbl... >>> declare @dt as date=getdate() >>> >>> 1) >>> >>> select * from tbl >>> >>> where HeadlineStartDate >= @dt and >>> >>> HeadlineEndDate<dateadd(d,1,@dt) >>> >> Wouldn't it be: >> >> where HeadlineStartDate < getdate() -- Make sure HeadlineStartDate is in >> the past >> and HeadlineEndDate > getdate() -- make sure HeadlineEndDate is in the >> future? >> >> These date comparisons have to be acurate to the minute. Will this method >> work? >> > > |