Retrieve Oracle datetime parameter as a date only on the database comm 1

[Lhuffst]

Hi I have been struggling for a long time to convert an oracle 10g datetime field to just a date field in Crystal. Things I have tried.

select to_date(fieldname) as HolidayDate from tablename

select to_Date(Trunc(To_Timestamp(HOLIDAY_DATE, 'yyyy-mm-dd hh24:mi:ss.ff'))) as HolidayDate
from tablename

select trunc(HOLIDAY_DATE)  as HolidayDate from tablename

I put these in a command file because I couldn't change the format of the field at the table level. I also checked the report options to make sure that the date format type was date only (suspect that this only applies to the display and not the underlying data).

No matter what I have tried, if I rightclick the command field it still shows as a datetime field. I need a date field because it is in a subreport that is being used against other date fields/formulas.

Can anyone help?? Thanks Lhuffst

 

[Turkbear]

Hi,
In a command you need to use Oracle syntax, so to_date is not correct when working on an existing DateTime field.

Your
TRUNC(Holiday-Date)
should result in just the date part as data,
but the field is still a DateTime

More examples of handling DateTime and Dates here:

http://psoug.org/reference/date_func.html

and

http://psoug.org/definition/TRUNC.htm

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Lhuffst]

Turbear Thank you for the info. I thought that I could not compare a datetime to a date field and that was what caused my problem. Based on your answer, I went back and used the table direct instead of using a command where I did the truncating and now it seems to work. Thanks for clearing up the confusion. lhuffst