Subject Re: TimeStamp vs. Date and Time
Author Adam
--- In, "ra8009" <ra8009@y...> wrote:
> If I use the TimeStamp field type, can I query for just the date or
> just the time?

Yes, but you need to use the built in cast function to convert the
timestamp data type to a date datatype.


select Cast(SomeTime as Date)
from SomeTable

Equally, you can just return the Time component by using cast
(SomeTime as Time), or a string representation cast(Sometime as
varchar(20)) etc etc.

> Does storing the date and time seperately have an
> advantage?

Generally not. It may make writing queries with primitive reporting
engines easier, but unless you have a real need to I wouldn't bother.
If you want to create an index on a date only field, and there is a
separate time field, then the date field will probably have bad
selectivity, so you may benefit from having a compound index. We have
a date representation in a few of our tables (before we found cast).

I would recommend you do not use a computed field for this though.
Because if you then run a query like

select *
from sometable
where somecalculateddate = ?

it will not be able to use the index on the "sometime" field. It is
pretty trivial to change the query logic to

select *
from sometable
where sometime >= ? and sometime < (?+1)

(where ? is a date)

Also note the boundary conditions, between is inclusive so it will
include midnight the next day.

Hope that helps