Change datetime to date

[kernal]

I have a datetime field (example: 08/06/2007 9:05:06AM) that I want to change so only the date is in a select statement (so I only get 08/06/2007). How do I write the expression?

Help is appreciated.

Thanks

 

[dbtoo2001]

select to_char(sysdate,'MM/DD/YYYY') from dual;

 

[kernal]

Hi dbtoo2001,

I have to use Peoplesoft query manager to write my queries and it says that I can't use FROM as part of my expression. Thanks for replying to my post. I guess I'll have to find some kind of workaround.

 

[Dagon]

I don't know exactly how your front end works, but you don't need to select the function from a table. You just apply it to your column in the normal select list e.g.

select
column1,
column2,
to_char(date_column, 'MM/DD/YYYY') as output_date,
...
from my_table

 

[dbtoo2001]

The point was to 'show' you the use of the formatting method. You hadn't mentioned the use of a third party reporting tool, you asked how to do it in a select statement - I showed you how to do it in a select statement. You'll need to refer to the 'Peoplesoft query manager' manual and read up on how to do it within the contraints of that tool. Maybe this can help?

http://beulah.doit.wisc.edu:6001/PSOL/htmldoc/eng/psbooks/tnvs/htm/tnvs11.htm#H3005

I've never used it.

here's the link to oracle's date formating:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/sql_elem.htm#34926

 

[Turkbear]

Hi,
Try:

select TRUNC(DateTimeField)

This returns only the DATE part..

Bear in mind that this is only a display format, the DATETIME field has an internal format defined by Oracle.





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

 

[kernal]

Thanks all. I'll post another message when I've had the chance to try them (most likely, next week).

 

[kernal]

Thanks all. I'm getting error messages for the expressions. I decided to do a workaround. In the query manager, it has a drop down box for expression type and date is an option. I didn't want to use the drop down option because the statement has <= to the date but I'm only getting the < date data. I thought if I could actually write the expression instead of using the drop down box then it might work. I just have to enter 1 day after the date I really want (i.e. so if I want data for 08/15 then I have to enter 08/16, even though, it has <=). Thanks again for taking the time to reply to my problem.

 

[Turkbear]

Hi,
Why is there any issue with the operator ( <= ) when your question invloved formatting the DateTime field returned by your Select statement?

Is that field also used in the WHERE clause?





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

 

[kernal]

I did have it as a field and in the where clause. Since I have a solution, I'm on to the next problem that hopefully, will be easier to solve. Thanks