SQL: select date rows that contain specific hour and minute-CodePudding

I am querying a table that has the date column as follows:

date
2021-03-08 05:05:31 00
2021-03-08 05:10:31 00

How can I select all the rows that contain 05:05 as the hour and minute in SQL? i.e. rows where hour = 05, and minute = 05. In this case it will be the first row.

CodePudding user response：

Q: How can I select all the rows that contain 05:05 as the hour and minute in SQL?

A: For MySQL, look in the MySql Date and Time functions. There, you'll find Extract().

You can use it as follows:

https://www.w3schools.com/sql/func_mysql_extract.asp

Extract the minute from a datetime:
SELECT EXTRACT(MINUTE FROM "2017-06-15 09:34:21");

This assumes that you're storing the column as a "Date" type.

Different RDBMS vendors have different Date/Time functions. You'll have to read the documentation and experiment to determine which syntax to use for your particular DB vendor and your particular table schema.

CodePudding user response：

You Can Use below Query for get Result as per your question .

There is DateName function in SQL and you can put this in your query as below.

CreatedDate is column name.. Example : Select * from #tmp1 where datename(hour,createdDate) =07 And datename(minute,CreatedDate) =07