date comparison

Is there a way to compare to a date variable with a 'flattened' date? What
I mean is, something like this:
select * from paysched where sdate = '02/24/2003'
now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
works. But what if it were '02/24/2003' 09:22:02'? I could do a 'between',
I know, but is there any way to 'flatten' the sdate in the select so that it
only contains the date, not the time, element?
Tx for any help.
Bernie YaegerWhile you can do something like that, you don't want to. The reason is the
any attempt to manipulate the value of the column in the where clause will
cause the optimizer to ignore any potentially useful indexes on that column.
Your best bet is to use between.
Less efficiently:
where convert(char(8), sdate, 112) = '20030224' -- note - string to string
comparison
where cast(convert(char(8), sdate, 112) as datetime) = '20030224' -- note -
string constant promoted to datetime
where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
I'm sure there are others. BTW - you should format your strings as yyyymmdd
if you want them to be convertable to datetime without worrying about
regional or language or dateformat settings.
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> Is there a way to compare to a date variable with a 'flattened' date?
What
> I mean is, something like this:
> select * from paysched where sdate = '02/24/2003'
> now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
> works. But what if it were '02/24/2003' 09:22:02'? I could do a
'between',
> I know, but is there any way to 'flatten' the sdate in the select so that
it
> only contains the date, not the time, element?
> Tx for any help.
> Bernie Yaeger
>|||Hi Scott,
Tx for the advice. I had actually been working with convert to get it done
while awaiting a response. Re the optimizer - this is a very small table
(maybe 150 rows) so it is not significant here, but I appreciate your
recommendation.
Tx again,
Bernie
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OjyNr6ZmDHA.3288@.tk2msftngp13.phx.gbl...
> While you can do something like that, you don't want to. The reason is
the
> any attempt to manipulate the value of the column in the where clause will
> cause the optimizer to ignore any potentially useful indexes on that
column.
> Your best bet is to use between.
> Less efficiently:
> where convert(char(8), sdate, 112) = '20030224' -- note - string to string
> comparison
> where cast(convert(char(8), sdate, 112) as datetime) = '20030224' --
note -
> string constant promoted to datetime
> where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
> I'm sure there are others. BTW - you should format your strings as
yyyymmdd
> if you want them to be convertable to datetime without worrying about
> regional or language or dateformat settings.
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> > Is there a way to compare to a date variable with a 'flattened' date?
> What
> > I mean is, something like this:
> >
> > select * from paysched where sdate = '02/24/2003'
> >
> > now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
> > works. But what if it were '02/24/2003' 09:22:02'? I could do a
> 'between',
> > I know, but is there any way to 'flatten' the sdate in the select so
that
> it
> > only contains the date, not the time, element?
> >
> > Tx for any help.
> >
> > Bernie Yaeger
> >
> >
>|||Scott,
FYI, in all your three examples you do not have the datetime column "alone" in the WHERE clause.
This will mean that SQL Server cannot use an index for that search argument. My favorite is:
DECLARE @.TheDate
SET @.TheDate = '20030523'
WHERE dtcol >= @.TheDate AND dtcol < DATEDADD(day, 1, @.TheDay)
Note that the column is not involved in any type of calculation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Scott Morris" <bogus@.bogus.com> wrote in message news:OjyNr6ZmDHA.3288@.tk2msftngp13.phx.gbl...
> While you can do something like that, you don't want to. The reason is the
> any attempt to manipulate the value of the column in the where clause will
> cause the optimizer to ignore any potentially useful indexes on that column.
> Your best bet is to use between.
> Less efficiently:
> where convert(char(8), sdate, 112) = '20030224' -- note - string to string
> comparison
> where cast(convert(char(8), sdate, 112) as datetime) = '20030224' -- note -
> string constant promoted to datetime
> where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
> I'm sure there are others. BTW - you should format your strings as yyyymmdd
> if you want them to be convertable to datetime without worrying about
> regional or language or dateformat settings.
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> > Is there a way to compare to a date variable with a 'flattened' date?
> What
> > I mean is, something like this:
> >
> > select * from paysched where sdate = '02/24/2003'
> >
> > now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
> > works. But what if it were '02/24/2003' 09:22:02'? I could do a
> 'between',
> > I know, but is there any way to 'flatten' the sdate in the select so that
> it
> > only contains the date, not the time, element?
> >
> > Tx for any help.
> >
> > Bernie Yaeger
> >
> >
>|||Exactly - I was giving the less efficient examples. The recommendation was
to use between (same as >= and <=).
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:Oc$4YlfmDHA.3312@.tk2msftngp13.phx.gbl...
> Scott,
> FYI, in all your three examples you do not have the datetime column
"alone" in the WHERE clause.
> This will mean that SQL Server cannot use an index for that search
argument. My favorite is:
> DECLARE @.TheDate
> SET @.TheDate = '20030523'
> WHERE dtcol >= @.TheDate AND dtcol < DATEDADD(day, 1, @.TheDay)
> Note that the column is not involved in any type of calculation.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
news:OjyNr6ZmDHA.3288@.tk2msftngp13.phx.gbl...
> > While you can do something like that, you don't want to. The reason is
the
> > any attempt to manipulate the value of the column in the where clause
will
> > cause the optimizer to ignore any potentially useful indexes on that
column.
> > Your best bet is to use between.
> >
> > Less efficiently:
> > where convert(char(8), sdate, 112) = '20030224' -- note - string to
string
> > comparison
> > where cast(convert(char(8), sdate, 112) as datetime) = '20030224' --
note -
> > string constant promoted to datetime
> > where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
> >
> > I'm sure there are others. BTW - you should format your strings as
yyyymmdd
> > if you want them to be convertable to datetime without worrying about
> > regional or language or dateformat settings.
> >
> > "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> > news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> > > Is there a way to compare to a date variable with a 'flattened' date?
> > What
> > > I mean is, something like this:
> > >
> > > select * from paysched where sdate = '02/24/2003'
> > >
> > > now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
> > > works. But what if it were '02/24/2003' 09:22:02'? I could do a
> > 'between',
> > > I know, but is there any way to 'flatten' the sdate in the select so
that
> > it
> > > only contains the date, not the time, element?
> > >
> > > Tx for any help.
> > >
> > > Bernie Yaeger
> > >
> > >
> >
> >
>|||left off a bit - between is the same ... assuming the appropriate boundaries
are used.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%2375zDtimDHA.3256@.tk2msftngp13.phx.gbl...
> Exactly - I was giving the less efficient examples. The recommendation
was
> to use between (same as >= and <=).
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:Oc$4YlfmDHA.3312@.tk2msftngp13.phx.gbl...
> > Scott,
> >
> > FYI, in all your three examples you do not have the datetime column
> "alone" in the WHERE clause.
> > This will mean that SQL Server cannot use an index for that search
> argument. My favorite is:
> >
> > DECLARE @.TheDate
> > SET @.TheDate = '20030523'
> > WHERE dtcol >= @.TheDate AND dtcol < DATEDADD(day, 1, @.TheDay)
> >
> > Note that the column is not involved in any type of calculation.
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Scott Morris" <bogus@.bogus.com> wrote in message
> news:OjyNr6ZmDHA.3288@.tk2msftngp13.phx.gbl...
> > > While you can do something like that, you don't want to. The reason
is
> the
> > > any attempt to manipulate the value of the column in the where clause
> will
> > > cause the optimizer to ignore any potentially useful indexes on that
> column.
> > > Your best bet is to use between.
> > >
> > > Less efficiently:
> > > where convert(char(8), sdate, 112) = '20030224' -- note - string to
> string
> > > comparison
> > > where cast(convert(char(8), sdate, 112) as datetime) = '20030224' --
> note -
> > > string constant promoted to datetime
> > > where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
> > >
> > > I'm sure there are others. BTW - you should format your strings as
> yyyymmdd
> > > if you want them to be convertable to datetime without worrying about
> > > regional or language or dateformat settings.
> > >
> > > "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> > > news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> > > > Is there a way to compare to a date variable with a 'flattened'
date?
> > > What
> > > > I mean is, something like this:
> > > >
> > > > select * from paysched where sdate = '02/24/2003'
> > > >
> > > > now sdate is a datetime column. It is '02/24/2003 00:00:00' and so
it
> > > > works. But what if it were '02/24/2003' 09:22:02'? I could do a
> > > 'between',
> > > > I know, but is there any way to 'flatten' the sdate in the select so
> that
> > > it
> > > > only contains the date, not the time, element?
> > > >
> > > > Tx for any help.
> > > >
> > > > Bernie Yaeger
> > > >
> > > >
> > >
> > >
> >
> >
>