ASE Database forum - Select statement question

Author

Select statement question

Paula

2005-10-27, 8:21 am

I appologize in advance if this is a really stupid question,
but I cannot seem to solve it!

I have a table where the column that is the primary key is
datetime data type. I need to write an sql statement to
retrieve the record using the primary key and nothing I have
tried works. The data was inserted using the getdate()
function. If I manually insert a record using just the
date, I am able to write the sql...select * from table where
date = '10/2/2005' and get the record.

I still have no luck if I select using the entire datetime
with milliseconds - select * from table where date =
'10/13/2005 11:00:36 AM'

Please help!

Thanks,
Paula

Robert Densmore

2005-10-27, 8:21 am

Try:
select *
from table
where date >= "10/2/2005" and date < "10/3/2005"

I try to make all date fields not include the time portion when
inserting/updating to avoid having to write range queries to get a
single day.

Bob

On 13 Oct 2005 06:59:50 -0700, Paula wrote:

>I appologize in advance if this is a really stupid question,
>but I cannot seem to solve it!
>
>I have a table where the column that is the primary key is
>datetime data type. I need to write an sql statement to
>retrieve the record using the primary key and nothing I have
>tried works. The data was inserted using the getdate()
>function. If I manually insert a record using just the
>date, I am able to write the sql...select * from table where
>date = '10/2/2005' and get the record.
>
>I still have no luck if I select using the entire datetime
>with milliseconds - select * from table where date =
>'10/13/2005 11:00:36 AM'
>
>Please help!
>
>Thanks,
>Paula

Bret Halford

2005-10-27, 8:21 am

You can now use the DATE datatype and save some space if you
don't care to store the time value.

-bret

Robert Densmore wrote:
[color=darkred]
> Try:
> select *
> from table
> where date >= "10/2/2005" and date < "10/3/2005"
>
> I try to make all date fields not include the time portion when
> inserting/updating to avoid having to write range queries to get a
> single day.
>
> Bob
>
> On 13 Oct 2005 06:59:50 -0700, Paula wrote:
>

Steve

2005-10-27, 8:21 am

I a little confused. Your select criteria does not include
milliseconds which would be required.

> I appologize in advance if this is a really stupid
> question, but I cannot seem to solve it!
>
> I have a table where the column that is the primary key is
> datetime data type. I need to write an sql statement to
> retrieve the record using the primary key and nothing I
> have tried works. The data was inserted using the
> getdate() function. If I manually insert a record using
> just the date, I am able to write the sql...select * from
> table where date = '10/2/2005' and get the record.
>
> I still have no luck if I select using the entire datetime
> with milliseconds - select * from table where date =
> '10/13/2005 11:00:36 AM'
>
> Please help!
>
> Thanks,
> Paula

Andrew Schonberger

2005-10-27, 8:21 am

Paula, Steve,

a DateTime field is what the documentation calls an "approximate
datatype". This means that it cannot be always written to full
precision as a string. For example, older versions were accurate to
1/300 of a second. If you try to divide one second by 300, you get an
infinitely repeating string of the digit 3. Internally, this makes
sense, but when you try to write it as milliseconds, you don't get
exactly the time you wanted.

Assume, for sake or argument, that the getdate() function was returning
11:00:36am plus 1/3 of a second at the time when your record was
inserted. If you look for "11:00:36.333" then it is less than the value
of the record. Or, if you look for 11:00:36.334 then it is more than the
value. The only way to catch it is by using a range between these two
dates.

The internal precision may seem excessive, but it still does not
guarantee uniqueness. No matter how many decimals Sybase would use below
the millisecond level, it could still happen that two insert operations
are carried out at about the same time ( remember: machines are also
getting faster). The recomendation is to avoid approximate datatypes as
primary keys. I would suggest an identity type as primary key. You can
still have the date for information purposes.

Andrew

Steve wrote:
[color=darkred]
>I a little confused. Your select criteria does not include
>milliseconds which would be required.
>
>
>

Bret Halford

2005-10-27, 8:21 am

DATETIME precision is still 1/300th of a second in
12.5.x and 15.0.x

-bret

Andrew Schonberger wrote:

> Paula, Steve,
>
> a DateTime field is what the documentation calls an "approximate
> datatype". This means that it cannot be always written to full
> precision as a string. For example, older versions were accurate to
> 1/300 of a second.

Sponsored Links