Get today’s date from Oracle 2

[Andrzejek]

I know I can get the current date from Oracle by:[tt]
SELECT CURRENT_DATE FROM DUAL[/tt] or[tt]
SELECT SYSDATE FROM DUAL[/tt]
That gives me the Date and Time, but I am interested in Date portion only.

I work on Stored Procedure that should e-mail some information to users 6 days prior to a date field in my data base. That date field does not have a Time portion (OK, it theoretically does have Time, but it is 00:00:00, so the field is a date of, let’s say ‘04/01/2014 00:00:00’ )

Right now I format SYSDATE to give me just the Date portion by TO_CHAR() then I turn it into Date with TO_DATE() so I can calculate how many days it is between today and the Date in my data base.
[tt]
SELECT SOME_DATE
FROM tblMyTable
WHERE (SOME_DATE = TO_DATE(TO_CHAR(SYSDATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') + 6);
[/tt]
Is there some easier way to get today’s Date from Oracle, i.e. [tt]'3/11/2014'[/tt] as Date?


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SamBones]

I believe the [tt]TRUNC()[/tt] function chops off the time portion. You can also give it a mask to specify which parts you are chopping off (truncating).

SELECT SOME_DATE
FROM   tblMyTable
WHERE  (SOME_DATE = TRUNC(SYSDATE) + 6);

 

[Andrzejek]

Thank you.

[tt] TRUNC(SYSDATE)[/tt] is the answer.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SamBones]

Oh, thanks for the star!

Just keep in mind that this might be safer, since even a single second will break the equal condition.

SELECT SOME_DATE
FROM   tblMyTable
WHERE  ([highlight #FCE94F]TRUNC([/highlight]SOME_DATE[highlight #FCE94F])[/highlight] = TRUNC(SYSDATE) + 6);

 

[Andrzejek]

Thanks for the warning, I see your point. But my dates do not have Time portion, it is all 00:00:00, and the Stored Procedure will be run just after midnight.

But that's good to know for the future if I need to deal with 'real' dates.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.