How to separate date and time from a datetime?

Question

I have a table with a datetime record. I need to select the record using date or time.

Let's say that I have this format 1/1/2001 13:33:00 in the database

My question is: If I enter the date, the time or both, I should obtain the rows that have either same date or the same time. How can I achieve this?

The convert function CONVERT(VARCHAR(10), Travel.Travel_DateTime, 110) returns the dates if their time is like the default 00:00:00

What is the type of the column in the database? VARCHAR? If so, why did you decide not to use a built-in DATE/TIME type? They're there to make life easier for users, including you. — Jonathan Leffler, Apr 03 '12 at 20:36
possible duplicate of [How to return the date part only from a SQL Server datetime datatype](http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype) — Corbin, Apr 03 '12 at 20:54

score 5 · Accepted Answer · edited May 23 '17 at 10:30

5

(Note that I'm assuming MySQL)

Just use the DATE and TIME functions:

SELECT blah FROM tbl WHERE DATE(some_datetime_field) = '2012-04-02';

That will select any rows such that the date part of some_datetime_field is 4 Apr 2012.

The same idea applies with TIME:

SELECT blah FROM tbl WHERE TIME(some_datetime_field) = '14:30:00';

So, to get all rows where the date is 5 Apr 2012 or the time is 2:30 PM, you just combine the two:

    SELECT blah FROM tbl WHERE DATE(some_datetime_field) = '2012-04-02' OR TIME(some_datetime_field) = '14:30:00';

---Edit---

For SQL server, you should be able to use:

CONVERT(DATE, some_datetime_field) = '2012-04-02'

(From How to return the date part only from a SQL Server datetime datatype)

edited May 23 '17 at 10:30

Community

1
1

answered Apr 03 '12 at 20:28

Corbin

33,060
6
68
78

@RasheedKhalilSalem your question should specify that then. I tend to assume questions that do not specify are MySQL :). – Corbin Apr 03 '12 at 20:51
thanks a lot its working and i am sorry for not specified that i am using sql i thought that the tag in enough – Rasheed Khalil Salem Apr 03 '12 at 22:22
CONVERT(time ,travel_datetime) for the time thank you very much :) – Rasheed Khalil Salem Apr 03 '12 at 22:28

score 2 · Answer 2 · answered Apr 03 '12 at 20:33

say you are passing date and time in l_DATE and l_TIME variable ..

then you can use following query..

select * from your_table 
where to_char(date_field,'DD/MM/YYYY') = l_DATE 
   or to_char(date_field ,'HH:MI:SS') = l_TIME

If you are using Oracle database as DBMS then you can use above query and it should give you your desired answer/ result...

How to separate date and time from a datetime?

2 Answers2