Problem searching with date criteria

[ashab02]

Hello
I have a set of data and when I convert the column with no where clause it works fine, the whole set converts successfully.
However when i use a cluase, i.e >= '20080701' then it fails 'Syntax error converting datetime from character string.'

I have the following query:

select  convert(datetime,t2.data,103)
from table1 t1
inner join table2 t2 on t1.code =t2.code 
where convert(datetime,t2.data,103) >= '2008070'

If you take the where clause off it all converts and brings back rows but as soon as you put the where clause in it fails. I have used different ways of inputting the where clause but with no joy

Anyone got any ideas?

 

[bborissov]

This string is not full:

'2008070'

You miss the last digit of the DAY.
That should work:

select  convert(datetime,t2.data,103)
from table1 t1
inner join table2 t2 on t1.code =t2.code
where t2.data >= '2008070[b]1[/b]'

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.