erikTsomik
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
ASKER
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.
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')
@Erik
The column is supposed to be datetime. The right side is turned into valid [date]time to compare against it.
The column is supposed to be datetime. The right side is turned into valid [date]time to compare against it.
ASKER
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')
bodyData BETWEEN CONVERT(datetime, '7:29 AM') and CONVERT(datetime, '8:41 AM')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)