Is this kind of coding correct?

SELECT *
FROM mainAim
WHERE CONVERT(varchar,Create_time, 120) = '2004-004-23 07:00:05'

I try to bring out the data related to the where clause but nothing showed up.
Is my where clause there got problem?
the data type for the field Create_time is datetime.
Please help.
Thanx

Not too sure if this is your only issue, but I think CONVERT returns date data under the "120" style spec as yyyy-mm-dd hh:mi:ss

Your hardcoded date is in the form yyyy-mmm-dd hh:mi:ss (note that you have 3 digits for the month.

Hope this helps.

Tim

Hehe.................
thanx..........................i'll go try and see.
Thanx again

datetime datatype converts implicitly to nvarchar. You shouldn't need to CAST or CONVERT to compare a datetime field, and is probably going to cause more problems then it's worth. By comparing a string containing a date/time to a datetime field, the string is converted implicitly to datetime and compared (this allows you to do greater-than, greater-than-or-equal-to, less-than, etc comparisons). But if you convert the datetime field to a string, then your "=" is doing a string level comparison, not a datetime level comparison. So a string level comparison of "2004-03-05" is different than "3/5/2004". But a datetime level comparison "2004-03-05" is equivilant to "3/5/2004".

Thanx for your advice.
I really appreciate it.
Thanx again

As a side note, the formats for CONVERT and CAST (which are functionally similar) are:

CAST ( expression AS data_type )
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

So converting a datetime to an nvarchar might look like:
CAST( myDate AS nvarchar(30) )
or
CONVERT( nvarchar(30), myDate )

Or as MrWizard mentioned, you can add a style to CONVERT for datetime conversions:

CONVERT( nvarchar(30), myDate, 120 )
Style 120 = ODBC canonical: yyyy-mm-dd hh:mi:ss(24h)

Thanx Seppuku.
I appreciate it