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
(Note that I'm assuming MySQL)
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';
For SQL server, you should be able to use:
CONVERT(DATE, some_datetime_field) = '2012-04-02'
say you are passing date and time in
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...