Date and Time Functions in SQL

-

Oracle has several functions that operate on "date" data types. These are some:

- add_months (f, n): adds a number of months to a date. If the second argument is positive, this number of months is added to the sent date; if it is negative, such number of months is subtracted from the sent date.


Example:

select add_months ('10 / 06/2020 ', 5) from dual; --returns "11/10/20"

select add_months ('10 / 06/2020 ', - 5) from dual; --returns "01/10/20"

select add_months ('30 / 01/2020 ', 1) from dual; - returns "02/29/20" since it is the last day of that month.

- last_day (f): returns the last day of the month of the date sent as an argument. 


Example:

 select last_day ('10 / 02/2020 ') from dual; - "02/29/20"

 select last_day ('10 / 08/2020 ') from dual; - "08/31/20"


- months_between (f1, f2): returns the number of months between the dates sent as an argument. 


Example:

select months_between ('19 / 05/2020 ',' 06/21/20 ') from dual; - returns -1.06451613 

- next_day (date, day): returns a date corresponding to the first day specified in "day" after the specified date. The following example finds the Monday following the specified date:


select next_day ('08/10/2020', 'MONDAY') from dual; - 08/17/20


- current_date: returns the current date. 

Example:

 select current_date from dual;


- current_timestamp: returns the current date

select current_timestamp from dual;

Returns: 07/22/20 07: 27: 20,973000000 AMERICA / BUENOS_AIRES


- sysdate: returns the current date and time on the Oracle server.


- systimestamp: return current date and time.


select systimestamp from dual;

Returns 07/22/20 07: 27: 45,012000000 -03: 00


- to_date: converts a string to data type "date".

 

Example:

select to_date ('05 -SEP-2019 10:00 AM ',' DD-MON-YYYY HH: MI AM ') from dual;

Returns 09/05/19


- to_char: convert a date to a character string.


Example:

select to_char ('10 / 10/2020 ') from dual;


- extract (part, date): returns the part (specified by the first argument) of a date. You can extract the year (year), month (month), day (day), hour (hour), minute (minute), second (second), etc.


Example:

select extract (month from sysdate) from dual;

returns the month number of the current date.

In Oracle: The arithmetic operators "+" (plus) and "-" (minus) can be used with dates.


For examples:

select sysdate-3: Returns 3 days before the current date.


select to_date ('15 / 12/2020 ') - 5 from dual;

Returns 12/10/20


These functions can be used by sending as an argument the name of a field of type date.

Comments

Post a Comment

Popular posts from this blog

Data Consistency and Inconsistency - What is Difference

-
Image
  Data Consistency: Definition 1: Data consistency means that the changes made to the different occurrences of data should be controlled and managed in such a way that all the occurrences have the same value for any specific data item. Data inconsistency leads to a number of problems, including loss of information and incorrect results. In the database approach, it is controlled because data is shared and consistency is controlled and maintained. Data Consistency Definition 2: Data consistency meaning is the validity, accuracy and usability of related data. It ensures that each user observes a consistent(Same) view of the data, including changes made by the user’s own transactions and transactions of other users. Types of Data Consistency: Point in time consistency Transaction consistency Application consistency Data Inconsistncy: Data Inconsistency Definition: Data inconsistency meaning is that different versions of the same data appear in d...
Read more

Cardinality in DBMS – max/minimum cardinality & examples

-
Image
  Logical Relationship in Tables: Database tables have a major advantage to bridge them with some logical relationship so to filter the records. These relationship allowed us to access one table data using another table records(via columns or rows). In other words logical filtration is called cardinality DBMS. Read Also: Relational Model in DBMS Definitions of Cardinality in DBMS: Definition 1: A number that represents, one instance of first entity(table) is related to how many instances of the second entity, is known as cardinality in dbms. Definition 2: Cardinality is a relationship or join/bridge between rows of  1 table to the rows of another table. via some column (e.g. User Table column {user_id} in another table Posts to filter the posts by user_id). Types of Cardinality in DBMS: There are three types of Cardinality in DBMS. one-to-one one-to-many many-to-one many-to-many Read Also: Degree of Relationship in DBMS ...
Read more

Relational Model in DBMS

-
Image
  Relational Model (RM) represents the database as a collection of relationships. A relationship is nothing more than a table of values. Each row of the table represents a collection of related data values. These rows of the table denote a real-world entity or relationship. The table name and column names are useful for interpreting the meaning of the values in each row. Data are represented as a set of relationships. In the relational model, the data are stored as tables. However, the physical storage of data is independent of how data is organized logically. Some popular relational database management systems are: DB2 and Informix Dynamic Server – IBM Oracle and RDB – Oracle SQL Server and Access – Microsoft Concepts of relational model in DBMS Attribute: Each column in a table. Attributes are the properties that define a relationship. e.g. Student_Rollno, NAME, etc. Tables – In the Relational model the relationships are saved in table format. It ...
Read more