Convert varchar 114 on datetime equals what?

Question

I have trouble in setting the equal condition on a datetime field after converting it to hh:mm:ss.mmm:

select top 1 convert(varchar, timestamp, 114) from TEST_TABLE

/* returns a single entry of 11:33:35:000 */

Then I want to find all entries with that timestamp:

select * from TEST_TABLE where convert(varchar, timestamp, 114) = '11:33:35.000'

/* returns nothing */

I've also tried the format of '11:33:35' (without the mmm) on the R.H.S. of the equal sign, no luck. I'm running these queries in MS SQL Server Management Studio 2008 if that matters. But I need a way that works cross platform as ultimately I'm running a Perl script to perform the query via the DBI module.

Answer 1

11:33:35:000 is not the same as 11:33:35.000

try

select * 
from TEST_TABLE 
where convert(varchar, timestamp, 114) = '11:33:35:000'

I prefer style 108 myself

select convert(varchar, GETDATE(), 108)

15:29:00

Answer 2

You are comparing strings, so they have to be exactly the same:

select * from TEST_TABLE where convert(varchar, timestamp, 114) = '11:33:35:000'

Notice the colon instead of the period in the string literal.