Solved

Time format in sql where clause

Posted on 2011-02-16
6
496 Views
Last Modified: 2012-05-11
I have the form where I have the time picker. The time that get passed to the sql s in this format 5:30 AM. How do i create a where clause in sql to search for this value
0
Comment
Question by:erikTsomik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34911857
Pass the time in a parameter (best)
Otherwise, if it is varchar, then, depending on what you are comparing it to

WHERE time_column = CONVERT(datetime, '5:30 AM')

WHERE time_column > CONVERT(datetime, '5:30 AM')

dateadd(d, -datediff(d, 0, getdate()), time_column) < CONVERT(datetime, '5:30 AM')

etc

The last one compares that the TIME portion of a datetime field is less than 5:30 am (regardless of date)
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 34912111
Also0 my data type for that fileld is datetime so the data that get stored (time portion) is in 24 hour clock . I think will need to convert left side of the where clause to match right side of the query
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34912287
>>  How do i create a where clause in sql to search for this value

Do you want all the records logged after 5.30 AM? The last query by cyberkiwi should work to compare the time part only. You can also try like this.

CONVERT(datetime,CONVERT(varchar(20),time_field,14)) > select CONVERT(datetime,'05:30 AM')

Open in new window

0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34912887
@Erik

The column is supposed to be datetime. The right side is turned into valid [date]time to compare against it.
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 34917391
How can i do in between operation. I am doing the following but it returns nothing. and I do have the records in the dataabse

bodyData BETWEEN CONVERT(datetime, '7:29 AM') and CONVERT(datetime, '8:41 AM')
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 34917515
try this.
CONVERT(datetime,CONVERT(varchar(20),bodyData ,14)) BETWEEN CONVERT(datetime, '7:29 AM') and CONVERT(datetime, '8:41 AM')

Open in new window

0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

729 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