Link to home
Start Free TrialLog in
Avatar of erikTsomik
erikTsomikFlag for United States of America

asked on

Time format in sql where clause

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
Avatar of cyberkiwi
Flag of New Zealand image

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')


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


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


The column is supposed to be datetime. The right side is turned into valid [date]time to compare against it.
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')
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial