?
Solved

Sysdate without time data

Posted on 2007-08-04
6
Medium Priority
?
1,777 Views
Last Modified: 2013-11-05
I need to pull sysdate as just a date (no time info)

What's the syntax?

Thanks,
0
Comment
Question by:Weller0123
6 Comments
 
LVL 7

Expert Comment

by:gattu007
ID: 19633330
select sysdate from dual;


the above will not show u time.

0
 
LVL 7

Expert Comment

by:gattu007
ID: 19633338
select to_char(sysdate,'dd-mon-yyyy') from dual;
0
 
LVL 12

Accepted Solution

by:
jwahl earned 2000 total points
ID: 19633493
SELECT TRUNC(SYSDATE) FROM dual;
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 19637007
select to_char(sysdate,'dd-mon-yyyy') from dual;

I think this is the best answer because this does not depend on the nls_date_format
setttings otherwise it depends on the format which has been set to nls_date_format.

select sysdate, trunc(sysdate), round(sysdate)
from dual;
-- will return date value according to the format set in nls_date_format. If you are
going to use this, then make sure that you have set your nls_date_format accordingly
which execludes time.

0
 
LVL 12

Expert Comment

by:jwahl
ID: 19637044
i agree with nav_kum_v, but it depends on what type you're expecting.
if you want DATE, you can use either

TRUNC(SYSDATE)
or
TO_DATE(TO_CHAR(SYSDATE, 'dd-mon-yyyy'), 'dd-mon-yyyy')

TO_CHAR(SYSDATE, 'dd-mon-yyyy') will return a VARCHAR2 string ...


0
 
LVL 32

Expert Comment

by:awking00
ID: 19637914
You say you want to "pull" sysdate as <just a date (no time info)>
What are you going to do with it? If you just want to display it without any time shown, the default setting will likely manage that and, if not, the TO_CHAR(SYSDATE, 'dd-mon-yyyy') [or any other format mask without a time element] will accomplish that regardless of the default. If you are using it to compare with some other date datatype, the use of trunc(sysdate) will still have a time element (of midnight) so you may have to also use the trunc function on the "other date" to compare equality. Greater than and less than comparisons will also require some manipulation. Let us know of your intent.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question