current_date question 1

[mikedaruke]

How can I get

current_date to just be MM/DD/YYYY ?

Right now it is in that format but it has the time on it as well. I am trying to do equal to on the date but it never returns because it is comparing the date and time and my field only has the date.

 

[jaxtell]

You need to TO_CHAR it with the proper formatting

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

 

[SantaMufasa]

Mike,

First of all, are both of the expressions to which you are comparing data types of DATE? If so, then to compare just the non-TIME (i.e., DATE) portions of DATE expressions to one another, then you TRUNCate the expression(s):

SELECT ...
 WHERE TRUNC(SYSDATE) = TRUNC(<your date expression>);

Let us know how this function works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[mikedaruke]

that helped thanks.

I couldn't move it to Char because when I compared it, Oracle did not know that 1/01/2008 was greater than 12/1/2007

Once I put it in date format it worked.

 

[SantaMufasa]

Actually, Mike, you can successfully compare dates that you transform using TO_CHAR as long as you perform the transformation using "well-behaved" high-order-to-low-order magnitudes:

SELECT ...
 WHERE TO_CHAR(SYSDATE,'YYYYMMDD')=
       TO_CHAR(<your date expression>,'YYYYMMDD');

Remember, the methods you use for comparison in a WHERE clause need not bear any resemblance to the method you use for displaying your DATEs.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]