How to find time greater than 15:00:00 in datetime

Question

Hello,
I have a datetime field in my table; for example, my datetime field gives me the datetime value as
2021-12-22 07:38:07.180. I would like to find rows contains any time greater than 3pm or 15pm on 2021-12-22 then May I ask if my statement below is correct or May you please help to correct it?

select * from mytable
 where mydatetime > 2021-12-22 15:00:00.180

Many Thanks,
Du

Answer 1

Did you put single quotation marks?

select * 
from mytable
where mydatetime > '2021-12-22 15:00'

Answer 2

Do you want to find all date/time values after 3PM or just on a particular day? For a particular day

where mydatetime > '2021-12-22 15:00:00'

For any day you just need to isolate the time portion. In SQL 2008+ you can use the TIME type.

where CONVERT(Time, mydatetime) > '15:00:00'

Answer 3

Hi,

I have a datetime field in my table;

Unfortunately we do not have your table or access to your server and we cannot read mind. Moreover you can save us time by providing the right information instead of stories (code instead of description)

(1) HIGHLY IMPORTENT!!! Please always when you have questions regarding queries, provide queries to create the relevant table(s) and queries to insert some sample data. In addition we need to get the description of the expected result SET which you want to get according to that sample data.

(2) Just a general comment (off-topic) : the phrase field is used for forms (for example in a web site you can have fields to fill, which in tables we have columns and rows. It is a common mistake since usually (not neccererily but in most cases) we use form with fields in order to insert data to table and the name of the field usually fits the name of the columns.

back to the question,

I would like to find rows contains any time greater than 3pm or 15pm on 2021-12-22

In the meantime please check the following sample of query. If this not cover your needs then please provide the missing information (point 1):

SELECT <columns list and never use star *> 
FROM Table_Name
WHERE mydatetime > CONVERT(DATETIME, '2021-12-22 15:00:00', 120)

Answer 4

Hi，@minh tran
Glad to see that your problem has been resolved!
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Note:Time format data should be considered in milliseconds. View the field type datetime in the database, format the data when querying the data like:
convert(datetime,mydatetime ,120) , and then execute.

Regards,
LiHong