Date Field:

Hi,

Database Gurus, may be too naive question but facing a problem with Date field when generating report(Crystal reports) on oracle database. Since oracle stores even TIME in date data type, so when users just enter time the report displays the field as 30-dec-1899 with year as "1899".

Configuration:
Vb5.0
Nt4.0 Workstation
Oracle 7.3.4

My question is:
1)How is that for this timestamp field I am facing a problem with century?.
2)In my database tables I do have date fields but their I don't get any problem with century when printing or querying database.
3)Have any one else encountered such a problem? Am I missing something.

Any help and early reply will be much appreciated.

Regards & Thanks.

Dates are defaulted to 12/31/1899, you can work around this by using a format statement that displays just the time in crystal reports something like sp = format(YourDate,"HH:MM") or you can use built in Oracle functions to return only the Hours and minutes in your query. I would opt for the second option as it will perform faster.

Sincere thanks for the reply. I had a close look at the functions that are provided with crystal reports, and I did'nt comes across a function that allows to format the returned date---its just unfortunate.Can you pls. eleborate your second option as I will be interacting from crystal report via an ODBC layer.

in SQL server you would do a
Select case isnull(YourDate) then " : "
else convert(char(2),(datepart("HHH",Yourdate)) + ":" +convert(char(2),(datepart("HHH",Yourdate))
end
Hours
from table
This would provide your date back in a preformatted sql statement. In Oracle you need to test your date in the select statment for null and if it is not null use string manuipulation in your sql statement to return the value you need.

Sincere thanks for the reply, they were quite useful. But the issue is how can Crystal Report recognize the SQL statement and the Database related functions. Crystal report has its own syntax for SQL and its own standarad functions. Anyway after quite a bit of working I found a solution but very bad professional wise, while I require only the time stamp to be displayed I do the timestamp field right justified and reduce the width of the display field so that only the time stamp is visible. Any loss of data no way its just we are hiding a part of data--which definately very bad way but then no way to go.

Can anyone give a thought on this:
Oracle database has an initializarion parameter called NLS_DATE_FORMAT, can anyone throw some light on this topic.

A very sincere THANKS in advance for an early reply.