How to separate date and time from a datetime?

Go To StackoverFlow.com

1

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

2012-04-03 20:25
by Rasheed Khalil Salem
please tell that how you are passing date or time value . - pratik garg 2012-04-03 20:28
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 2012-04-03 20:36
possible duplicate of How to return the date part only from a SQL Server datetime datatypeCorbin 2012-04-03 20:54


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)

2012-04-03 20:28
by Corbin
thanks a lot but i am using sql serve - Rasheed Khalil Salem 2012-04-03 20:47
@RasheedKhalilSalem your question should specify that then. I tend to assume questions that do not specify are MySQL :) - Corbin 2012-04-03 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 enoug - Rasheed Khalil Salem 2012-04-03 22:22
CONVERT(time ,travel_datetime) for the time thank you very much : - Rasheed Khalil Salem 2012-04-03 22:28


1

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

2012-04-03 20:33
by pratik garg
thanks a lot but i am using sql serve - Rasheed Khalil Salem 2012-04-03 20:46