Reserved Words

[Sue Lister]

I am trying to retrieve data on a datetime field.  The field is called transaction_date.  I recieve no data.  However, within the same table I can retrieve data on a datetime field called completion_date.  They are exactly the same accept for their names.  I have tried this in both Powerbuilder and WISQL with the same result - no data.   My question:  Is transaction_date a reserved word?    Has anyone heard of this happening?
 
 

[Bret Halford]

Hi Sue,

Could you show us what you mean by "no data" (ie, what is the query
you are running and what does the server return)?

if you issue

select transaction_date from mytable

does the server say "(0 rows affected)"?

Are you trying to specify a specific date in a where clause
and getting no results?

select * from mytable where transactio_date = "12/12/1997"

--
Bret Halford Imagine my disappointment
Sybase Technical Support in learning the true nature
3665 Discovery Drive of rec.humor.oracle...
Boulder, CO 80303

[Sue Lister]

Hi Bret:

Sorry for the lack of details. When I select transaction_date from prs_job,
I get all transaction dates returned. When I add the where clause I get 0
rows affected. When I use the same select statement but change the field
name to completion_date which has the exact same attributes and add the
where clause I get a result set. I'm also using Sybase System 11.

Here are my select statements:

Select transaction_date from prs_job

25 rows affected.

Select transaction_date from prs_job where transaction_date = '11/05/98'

0 rows affected.

The information is there and nothing is returned. I can also set a filter
in PowerBuilder and get nothing returned. However, when I issue the same
select statement but change the field name to completion_date in Sybase and
PowerBuilder I get the following:

Select completion_date from prs_job where completion_date = '11/01/99'

29 rows affected.

Thanks in advance for your help. I hope I have provided enough information
this time. I just don't understand what is happening. I want to know for
sure if this has to do with the reserved word transaction before I change
all of my field names and stored procedures.

Sue

[Stephen Simpson]

Sue,

try using a range for the query. Datetimes store times as well, and if a
time component is not specified, 12:00 AM is assumed.

Try

where transaction_date => '11/05/98' and transaction_date < '12/05/98' -- I
assume dmy format

or, if you do not have an index on the date field, you could attempt:

where datediff(dd,transaction_date,'11/05/98) = 0

Steve

--
Stephen Simpson, Simpson Professional Services
127/BH18 8JE/England
Tel: +44(0)1202 777417
Fax: +44(0)1202 604861
Mobile: +44(0)7771 758417
email:s...@londonmail.com
http://www.sps.mcmail.com
Sue Lister wrote in message <366CA86C...@aol.com>...

[Sue Lister]

Stephen:

Thank you. Yes, I can see how Sybase assumes 12:00. All the times in the
completion_date field are indeed 12:00 am. I was wondering if this had anything
to do with the fact that I was able to get those records. I have
transaction_date in about 50 tables and am grateful that I didn't go down the
wrong road. Your solution did the trick.

Thanks again.

Sue

[John McVicker]

Sue,
To see the minute details of the stored value, I suggest doing
a convert to see the hh:mm:ss:ms part. As follows:

For that day's rows:
Select transaction_date = convert(varchar(26),transaction_date, 109)
from prs_job
where transaction_date BETWEEN '11/05/98' and '11/06/98'

And, for all rows:
Select transaction_date = convert(varchar(26),transaction_date, 109)
from prs_job

Is it really storing 12:00am in there with it?

John McVicker
Sybase Professional Services
Philadelphia, PA