selecting rows by DATETIME without providing the time?

On Aug 28, 11:37 am, yawnmoth wrote:
> Say I'm looking for all rows with a timestamp field from "Jun 4
> 2008". Since the timestamp field also contains a time (in this case,
> 8:08AM), the following doesn't work:
>
> SELECT *
> FROM comments
> WHERE timestamp - '2008-06-04'
>
> The following, however, does work:
>
> SELECT *
> FROM comments
> WHERE timestamp > '2008-06-04 0:0:0.000' AND timestamp < '2008-06-05
> 0:0:0.000'
>
> My question is... if I want to find all entries that take place on a
> select day, do I always have to specify a date range or is there a way
> to make Sybase figure out that I'm not interested in anything above
> and beyond what I put into the query matching?


I have you tried:

WHERE datediff(dd,timestamp,'2008-06-04') = 0

??
 >> Stay informed about: selecting rows by DATETIME without providing the time? 

On Aug 29, 9:22 am, ThanksButNo wrote:
> On Aug 28, 11:37 am, yawnmoth wrote:
>
>
>
>
>
> > Say I'm looking for all rows with a timestamp field from "Jun  4
> > 2008".  Since the timestamp field also contains a time (in this case,
> > 8:08AM), the following doesn't work:
>
> > SELECT *
> > FROM comments
> > WHERE timestamp - '2008-06-04'
>
> > The following, however, does work:
>
> > SELECT *
> > FROM comments
> > WHERE timestamp > '2008-06-04 0:0:0.000' AND timestamp < '2008-06-05
> > 0:0:0.000'
>
> > My question is...  if I want to find all entries that take place on a
> > select day, do I always have to specify a date range or is there a way
> > to make Sybase figure out that I'm not interested in anything above
> > and beyond what I put into the query matching?
>
> I have you tried:
>
> WHERE datediff(dd,timestamp,'2008-06-04') = 0
>
> ??- Hide quoted text -
>
> - Show quoted text -

No, that is a bad idea.
If you use a function on a column (in this case datediff) then an
index on that column can’t be used
 >> Stay informed about: selecting rows by DATETIME without providing the time? 

On Aug 29, 1:25 am, PDreyer wrote:
> On Aug 28, 8:37 pm, yawnmoth wrote:
>
>
>
>
>
> > Say I'm looking for all rows with a timestamp field from "Jun  4
> > 2008".  Since the timestamp field also contains a time (in this case,
> > 8:08AM), the following doesn't work:
>
> > SELECT *
> > FROM comments
> > WHERE timestamp - '2008-06-04'
>
> > The following, however, does work:
>
> > SELECT *
> > FROM comments
> > WHERE timestamp > '2008-06-04 0:0:0.000' AND timestamp < '2008-06-05
> > 0:0:0.000'
>
> > My question is...  if I want to find all entries that take place on a
> > select day, do I always have to specify a date range or is there a way
> > to make Sybase figure out that I'm not interested in anything above
> > and beyond what I put into the query matching?
>
> Yes, specify a date range e.g. for today
>
> where
> timestamp>=dateadd(dd,datediff(dd,'20000101',getdate()),'20000101')
>   and timestamp<dateadd(dd,datediff(dd,'20000101',getdate())
> +1,'20000101')- Hide quoted text -
>
> - Show quoted text -

You may also wish to try something like ...

WHERE convert(varchar,timestamp,112) = '2000010'

which may run faster / slower depending on your indexing, ASE version,
phase of the moon, etc.

Incidentally, if you really only care about the "day" portion of the
[small]datetime value, Sybase does support both a DATE and a TIME
value.
They don't seem to be commonly used due to some limitations I suspect
(I seem to recall that getting the data to bcp in is difficult, etc.),
and
of course it makes comparing / adding / subtracting rows with these
(in aggregate) unwieldy, but they are there.

HTH,

Keith
 >> Stay informed about: selecting rows by DATETIME without providing the time?