CODEWITHSUNDEEP
sqloracle-databasedatedatetimetime
The_Bear
The_Bear

Reputation: 173

How to display date and time from datetime column in SQL

I have a staff_registered column which includes both the date and the time of when staff registered. However I want to be able to retrieve the date and the time for all the staff who registered on 22-JAN-07 how can I show the time?

SELECT  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, TO_CHAR (SYSDATE,'DD MON YY 
HH:MI:SS') 
FROM EMPLOYEE
WHERE TO_CHAR (REGISTRATION_DATE, 'DD-MON-YY') = '22-JAN-07';

I've wrote this as a starting point, I know it's wrong as it's getting today's date not 22-JAN-07 or the time but I don't know how to correct it.

Upvotes: 0

Views: 824

Answers (3)

haag1
haag1

Reputation: 352

I believe you are just dealing with a typo/brain fart. You are selecting today's (SYSDATE) date in your select statement. You want to be selecting REGISTRATION_DATE. It won't matter what you search for because currently it will always return the current date until you change it to below.

SELECT  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, TO_CHAR (REGISTRATION_DATE,'DD MON YY HH:MI:SS') 
FROM EMPLOYEE
WHERE TO_CHAR (REGISTRATION_DATE, 'DD-MON-YY') = '22-JAN-07';

Unless I misunderstood your question...

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

Use TRUNC without TO_CHAR conversion with this filter condition :

WHERE TRUNC(REGISTRATION_DATE) = date'2018-09-05'

Since REGISTRATION_DATE is already a DATE type field, isn't it ?

Upvotes: 1

Constanza Garcia
Constanza Garcia

Reputation: 366

if I understood right you have a column that has a string that contains what you are looking for '05-SEP-2018'

If so I would suggest something like

SELECT  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, TO_CHAR (SYSDATE,'DD MON YY HH:MI:SS') 
FROM STUDENT
WHERE CONTAINS(TO_CHAR(REGISTRATION_DATE, '05-SEP-2018'))

Let me know if it helps

Upvotes: 0

Related Questions