INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Get today’s date from Oracle

Get today’s date from Oracle

(OP)

I know I can get the current date from Oracle by:
SELECT CURRENT_DATE FROM DUAL
or
SELECT SYSDATE FROM DUAL

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.

SELECT SOME_DATE
FROM tblMyTable
WHERE (SOME_DATE = TO_DATE(TO_CHAR(SYSDATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') + 6);

Is there some easier way to get today’s Date from Oracle, i.e. '3/11/2014' 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.

RE: Get today’s date from Oracle

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

CODE

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


RE: Get today’s date from Oracle

(OP)
Thank you.

TRUNC(SYSDATE) is the answer. smile

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.

RE: Get today’s date from Oracle

Oh, thanks for the star! bigsmile

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

CODE

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


RE: Get today’s date from Oracle

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close