By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,251 Members | 1,648 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,251 IT Pros & Developers. It's quick & easy.

Not able to update using LIKE % clause in MSSQL

P: 10
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
Jan 31 '08 #1
Share this Question
Share on Google+
3 Replies


deepuv04
Expert 100+
P: 227
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
Jan 31 '08 #2

Delerna
Expert 100+
P: 1,134
OOPs , my comment does not apply so I deleted
Jan 31 '08 #3

P: 10
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!!
Feb 1 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.