?
Solved

hour:min from datetime

Posted on 2011-10-04
7
Medium Priority
?
245 Views
Last Modified: 2012-05-12
Hi,
I would like to select all from a DateTime column where hour is 14 and minutes 10, i.e. 14:10.
It does not matter which date it is. How is best to do this?
0
Comment
Question by:johnkainn
  • 4
  • 2
7 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36909989
You where clause should be:

where left(convert(nvarchar(8), myDateTimeField, 108), 2) = '14'
0
 
LVL 7

Expert Comment

by:celazkon
ID: 36909990
Don't know if it's exactly the best, but it should work:

SELECT datetime FROM yourtablename WHERE datetime LIKE '%14:10%'
0
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 2000 total points
ID: 36910001

where left(convert(nvarchar(8), myDateTimeField, 108), 5) = '14:10'
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 6

Expert Comment

by:dr_linux
ID: 36910006
You really should use DATEPART function to do it right.
http://msdn.microsoft.com/en-us/library/ms174420.aspx
0
 
LVL 6

Expert Comment

by:dr_linux
ID: 36910027
For example
select * from <tablename> where DATEPART(minute, columnname) = 12
0
 
LVL 6

Expert Comment

by:dr_linux
ID: 36910047
Or for your example
select * from ABook where DATEPART(hour, DateReceived) = 14 AND DATEPART(minute, DateReceived) = 10
0
 
LVL 6

Expert Comment

by:dr_linux
ID: 36910062
Obviously substitute 'ABook' for your table name and 'DateReceived' for your column name in the example above.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question