How to get DATE Only From DATETIME Column

Here are the ideas to get DATE only from a DATETIME column. In this regard, you would need to know how to use TO_DATE and TO_CHAR functions.

Here are the SQL queries tested in Oracle; Useful if your project deals Dates extensively.

Table of Contents

1. Create Table
2. INSERT values
3. UPDATE values

Create Table

First, I have written an SQL query to create a Table. Next, I will insert values. Finally, I will update the Table values.

SQL Query to create a Table

CREATE TABLE RANI (
NAME CHAR(20),
ID NUMBER(5),
JOIN_DATE DATE
);

Also Read| 32 Complex SQL Queries for Practice

Insert Values

Next, I will insert values into a Table. Here Table name is RANI. While inserting, the magic function TO_DATE I will use to insert both date and time.

SQL query to insert values

INSERT INTO RANI VALUES('MOHAN', 12345, TO_DATE('05-MAR-22 18:00:00', 'DD-MM-YYYY HH24:MI:SS'));
INSERT INTO RANI VALUES('RAVI', 02345, TO_DATE('05-MAR-22 18:00:00', 'DD-MM-YYYY HH24:MI:SS'));
INSERT INTO RANI VALUES('ASHA', 12367, TO_DATE('05-MAR-22 18:00:00', 'DD-MM-YYYY HH24:MI:SS'));
INSERT INTO RANI VALUES('VANI', 12885, TO_DATE('05-MAR-22 18:00:00', 'DD-MM-YYYY HH24:MI:SS'));
INSERT INTO RANI VALUES('MADHU', 14445, TO_DATE('05-MAR-22 18:00:00', 'DD-MM-YYYY HH24:MI:SS'));

Next to verify the time inserted along with the Date. Here is a SELECT SQL query, if you run this you will get only DATE (excludes Time) even though the column has both Date and Time.

SELECT SQL query

Below is the query to get only Date from DATETIME column.

SELECT *  FROM RANI;

Interestingly, the output shows only the Date. In fact, you inserted both Date and Time.

NAME	ID	JOIN_DATE
MOHAN	12345	05-MAR-22
RAVI	2345	05-MAR-22
ASHA	12367	05-MAR-22
VANI	12885	05-MAR-22
MADHU	14445	05-MAR-22

Now, the below SQL query gets both Date and Time. Here is the query.

SELECT NAME, ID, TO_CHAR(JOIN_DATE, 'DD-MM-YYYY HH24:MI:SS') as J_DATE  FROM RANI;

The output of the query has both Date and Time.

NAME	ID	J_DATE
MOHAN	12345	05-03-0022 18:00:00
RAVI	2345	05-03-0022 18:00:00
ASHA	12367	05-03-0022 18:00:00
VANI	12885	05-03-0022 18:00:00
MADHU	14445	05-03-0022 18:00:00

In conclusion, while inserting, I have used TO_DATE. But for reporting both Date and Time, I have used TO_CHAR. So these ideas make good sense to you to understand the formatting concept.

Also Read | SQL Query: How to Use Locate and Substring Correctly

Update Values

In the following step, you will see how to change the date and time.

UPDATE RANI SET JOIN_DATE=TO_DATE('10-FEB-22 09:00:00','DD-MM-YYYY HH24:MI:SS') WHERE NAME='MOHAN';

The output of the query will be updated date and time.

SELECT NAME, ID, TO_CHAR(JOIN_DATE, 'DD-MM-YYYY HH24:MI:SS') as J_DATE  FROM RANI;

NAME	ID	J_DATE
MOHAN	12345	10-02-0022 09:00:00
RAVI	2345	05-03-0022 18:00:00
ASHA	12367	05-03-0022 18:00:00
VANI	12885	05-03-0022 18:00:00
MADHU	14445	05-03-0022 18:00:00

Related posts

Shopping

• Women tops cotton