CODEWITHSUNDEEP
sqlsql-server
Rasheed Khalil Salem
Rasheed Khalil Salem

Reputation: 45

How to separate date and time from a datetime?

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

Upvotes: 1

Views: 34180

Answers (2)

Corbin
Corbin

Reputation: 33447

(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)

Upvotes: 5

pratik garg
pratik garg

Reputation: 3342

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...

Upvotes: 2

Related Questions