EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

Oracle SYSDATE()

By Priya PedamkarPriya Pedamkar

Secondary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Golden Gate
    • Oracle Virtual Machine
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle?Primavera
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle ERP
    • Oracle ASM
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Home Data Science Data Science Tutorials Oracle Tutorial Oracle SYSDATE()

Oracle SYSDATE()

Introduction to Oracle SYSDATE()

The Oracle SYSDATE( ) function can be defined as a built-in function with no arguments in Oracle database which is used to return the current date and time set for the operating system on which the particular oracle database is installed and the data type returned in the output is of type DATE with the format which depends on the value of NLS_DATE_FORMAT initialization parameter and this function cannot be used with any CHECK constraint.

Syntax

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

So, after discussing the definition of the SYSDATE function, we will now look at the syntax of the function. The syntax is simple.

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (85,938 ratings)

SYSDATE

As, we can see in the above syntax there are no arguments associated with the SYSDATE function.

How does Sysdate Function Work in Oracle?

The SYSDATE function is one of the basic functions in oracle to find out the date based on the local system date. So, we can use SYSDATE function with SELECT statements in Oracle. One important point that we need to remember while using SYSDATE is that it cannot be used in the condition of the check constraint. So, when we execute a SELECT query having SYSDATE function. It extracts the system date which is the current date and time as shown in the local system or laptop where the Oracle database is installed and the same is returned in the output.

Examples to Implement Oracle SYSDATE()

Let us now look into various examples for Oracle SYSDATE function:

1. Sysdate Function to Get Date

In this scenario we will use the SYSDATE function to get the date of the current system in which the database is stored. So in this example we will see the current date of the system in which the database is stored. We are going to use a select statement with a dual table. Dual table as we know is a one-row one-column table present by default in the oracle database. Let us create a simple query for it.

Code:

select SYSDATE from dual;

Output: Let us now run the query in SQL developer and check the result.

Get Date

As we can see that the data is displayed in the output.

2. Using Sysdate to Calculate Age

In this scenario we will see how we can calculate the age of any person using the SYSDATE function. In order to do that we will see an example in which suppose the date of birth of a person is 12th April 1992. So we are going to use the SYSDATE function to calculate the age of the person on the present day. So, let us create a simple query using SYSDATE to get the age of the person.

Code:

select trunc((SYSDATE-TO_DATE('12-04-1992'))/365) AGE from dual;

Explanation: In the above query first we are converting the string type date of birth to DATE type as SYSDATE is DATE type value. After converting we subtract them and then divide the whole difference by 365 as there are 365 days in a year and TRUNC function which is a built-in function helps us to return the year.

Output: Let us run the query in SQL developer and check the result.

Oracle SYSDATE() - 2

As we can see in the above output screenshot the YEAR is returned in the output after the function is executed successfully

3. Sysdate Function Used with Where Condition

In this scenario we will discuss the fact that we can use SYSDATE function with conditional statements like WHERE clause with the SELECT statement. The example, in this case, will be extracting the employee id, employee name, and the SYSDATE when the query is being executed where the condition is that the employee should be from the Union Territory of ‘Delhi’. Let us create a query for this scenario.

Code:

select employee_id, name, sysdate from employee where city ='Delhi';

Output: Let us now execute the query in SQL developer and check the result.

Oracle SYSDATE() - 3

4. Using TO_CHAR with Sysdate to Extract only The Day of The Date

In the previous scenario and its examples we have seen how we can use the SYSDATE function with SELECT statement along with WHERE condition. In this scenario we will see how we can use a TO_CHAR function along with SYSDATE function to extract only the day from the SYSDATE function. One important note is that the value returned will not be DATE value as we are using TO_CHAR function. It will be a string value. The example is to extract the employee name, employee id and current day from the employee table where the city of the employee is ‘Mumbai’. Let us create a SELECT query for this task.

Code:

select employee_id, name, TO_CHAR(SYSDATE, ‘DD’) from employee where city ='Mumbai';

Output: Let us now execute the query in SQL developer and check the result.

Oracle SYSDATE() - 4

As we can see in the above screenshot it shows only the day in the third column of the result set.

5. Using Sysdate with Insert Statement

In the previous examples we have used only SELECT statements. In this case we are going to use a DML statement like INSERT statement as the SYSDATE function can be used to insert current date and time in the columns of the employee table. In this example we will insert date and time into the column of a table employee. Let us create a DML query for the same.

Code:

INSERT INTO employee VALUES('Suresh',Suzuki,'AD012', 'VH002', 'kolkata', '44', null, null , null, sysdate);

Explanation: As we can see in the above query the sysdate function is used in the insert statement. This function will insert the current date in the last column of the employee table.

Output: Let us run the query in SQL developer and check the result.

Oracle SYSDATE() - 5

As we can see in the output the SYSDATE function inserts the current date and time in the table.

Conclusion

In this article we discussed the definition and syntax of the SYSDATE function and how it works in the Oracle database. Later on, in the article we discussed the various scenarios along with an example for each scenario to understand better.

Recommended Articles

This is a guide to Oracle SYSDATE(). Here we discuss an syntax, how does Oracle SYSDATE() work with examples to implement. You can also go through our other related articles to learn more –

  1. Table in Oracle
  2. DISTINCT in Oracle
  3. Oracle LENGTH( )
  4. Oracle Self Join
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
All in One Financial Analyst Bundle- 250+ Courses, 40+ Projects4.8
2 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - Oracle Training (14 Courses, 8+ Projects) Learn More