Not able to update using LIKE % clause in MSSQL

[pks83]

Hi

I am relatively new to MSSQL.... when i am trying to fetch the data from table using this query.

update ticket set escalation=activity where escalation like '2008%31'

escalation and activity are both of datetime data type

Values in escalation column

2008-01-30 06:46:27.000
2008-01-30 09:24:36.000
2008-01-31 09:41:12.000
2008-01-31 10:07:00.000
2008-01-31 12:04:30.000
2008-01-31 12:34:58.000
2008-01-31 12:52:29.000

But when i run this query it updates the 7 rows

update ticket set escalation=activity where escalation like '%2008%'

Why is this so.... i need to have more detailed like clause so thati update correct rows.

Appreciate any help.

Thanks

[deepuv04]

Hi

I am relatively new to MSSQL.... when i am trying to fetch the data from table using this query.

update ticket set escalation=activity where escalation like '2008%31'

escalation and activity are both of datetime data type

Values in escalation column

2008-01-30 06:46:27.000
2008-01-30 09:24:36.000
2008-01-31 09:41:12.000
2008-01-31 10:07:00.000
2008-01-31 12:04:30.000
2008-01-31 12:34:58.000
2008-01-31 12:52:29.000

But when i run this query it updates the 7 rows

update ticket set escalation=activity where escalation like '%2008%'

Why is this so.... i need to have more detailed like clause so thati update correct rows.

Appreciate any help.

Thanks

since the datetime column you are comparison with text it is not working

so before comparing the column with like convert the datatype to varchar as follows

update ticket set escalation=activity where convert(varchar(20),escalation,20) like '2008%31'

or
update ticket set escalation=activity where convert(varchar(20),escalation,120) like '2008%31'


hope will help you..

thanks

[Delerna]

OOPs , my comment does not apply so I deleted

[pks83]

since the datetime column you are comparison with text it is not working

so before comparing the column with like convert the datatype to varchar as follows

update ticket set escalation=activity where convert(varchar(20),escalation,20) like '2008%31'

or
update ticket set escalation=activity where convert(varchar(20),escalation,120) like '2008%31'


hope will help you..

thanks

Thanks a lot it worked!!