Update in place with case statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update in place with case statement

Author

Topic

DeanT
Starting Member

13 Posts

Posted - 2012-02-29 : 09:42:19

I have been working on trying to figure out why I cannot update a varchar field in place with this statement:

UPDATE dbo.Customer
SET Hr = CASE
WHEN Hr = '-6' THEN '12:00 am - 1:00 am'
WHEN Hr = '-5' THEN '1:00 am - 2:00 am'
WHEN Hr = '-4' THEN '2:00 am - 3:00 am'
WHEN Hr = '-3' THEN '3:00 am - 4:00 am'
WHEN Hr = '-2' THEN '4:00 am - 5:00 am'
WHEN Hr = '-1' THEN '5:00 am - 6:00 am'
WHEN Hr = '0' THEN '6:00 am - 7:00 am'
WHEN Hr = '1' THEN '7:00 am - 8:00 am'
WHEN Hr = '2' THEN '8:00 am - 9:00 am'
WHEN Hr = '3' THEN '9:00 am - 10:00 am'
WHEN Hr = '4' THEN '10:00 am - 11:00 am'
WHEN Hr = '5' THEN '11:00 am - 12:00 pm'
WHEN Hr = '6' THEN '12:00 pm - 1:00 pm'
WHEN Hr = '7' THEN '1:00 pm - 2:00 pm'
WHEN Hr = '8' THEN '2:00 pm - 3:00 pm'
WHEN Hr = '9' THEN '3:00 pm - 4:00 pm'
WHEN Hr = '10' THEN '4:00 pm - 5:00 pm'
WHEN Hr = '11' THEN '5:00 pm - 6:00 pm'
WHEN Hr = '12' THEN '6:00 pm - 7:00 pm'
WHEN Hr = '13' THEN '1:00 pm - 2:00 pm'
WHEN Hr = '14' THEN '8:00 pm - 9:00 pm'
WHEN Hr = '15' THEN '9:00 pm - 10:00 pm'
WHEN Hr = '16' THEN '10:00 pm - 11:00 pm'
WHEN Hr = '17' THEN '11:00 pm - 12:00 pm'
ELSE NULL
END

Hr is defined as a varchar(512) field but I'm getting an error:

Msg 245, Level 16, State 1, Line 69 Conversion failed when converting the varchar value '12:00 am - 1:00 am' to data type int.

http://www.local-developers.com

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-29 : 10:12:54

I believe you when you say that the data type of column Hr is varchar(512). But, just for laughs, can you run this query to be sure?

SELECT TOP 1 SQL_VARIANT_PROPERTY(Hr,'basetype') FROM dbo.Customer;

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-02-29 : 10:20:26

Is that your entire statement / stored proc? runs like a champ on my local server.


create table dbo.Customer (hr varchar(512))

insert into Customer (hr)
values ('-2'),('-4'),('3'),('7'),('14')

select * from Customer

UPDATE dbo.Customer
SET Hr = CASE
WHEN Hr = '-6' THEN '12:00 am - 1:00 am'
WHEN Hr = '-5' THEN '1:00 am - 2:00 am'
WHEN Hr = '-4' THEN '2:00 am - 3:00 am'
WHEN Hr = '-3' THEN '3:00 am - 4:00 am'
WHEN Hr = '-2' THEN '4:00 am - 5:00 am'
WHEN Hr = '-1' THEN '5:00 am - 6:00 am'
WHEN Hr = '0' THEN '6:00 am - 7:00 am'
WHEN Hr = '1' THEN '7:00 am - 8:00 am'
WHEN Hr = '2' THEN '8:00 am - 9:00 am'
WHEN Hr = '3' THEN '9:00 am - 10:00 am'
WHEN Hr = '4' THEN '10:00 am - 11:00 am'
WHEN Hr = '5' THEN '11:00 am - 12:00 pm'
WHEN Hr = '6' THEN '12:00 pm - 1:00 pm'
WHEN Hr = '7' THEN '1:00 pm - 2:00 pm'
WHEN Hr = '8' THEN '2:00 pm - 3:00 pm'
WHEN Hr = '9' THEN '3:00 pm - 4:00 pm'
WHEN Hr = '10' THEN '4:00 pm - 5:00 pm'
WHEN Hr = '11' THEN '5:00 pm - 6:00 pm'
WHEN Hr = '12' THEN '6:00 pm - 7:00 pm'
WHEN Hr = '13' THEN '1:00 pm - 2:00 pm'
WHEN Hr = '14' THEN '8:00 pm - 9:00 pm'
WHEN Hr = '15' THEN '9:00 pm - 10:00 pm'
WHEN Hr = '16' THEN '10:00 pm - 11:00 pm'
WHEN Hr = '17' THEN '11:00 pm - 12:00 pm'
ELSE NULL
END

OUTPUT:


hr
-2
-4
3
7
14

hr
4:00 am - 5:00 am
2:00 am - 3:00 am
9:00 am - 10:00 am
1:00 pm - 2:00 pm
8:00 pm - 9:00 pm

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 11:22:41

Column Hr is OBVIOUSLY defined as int, not varchar

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-29 : 11:36:25

It could also be that Hr is indeed varchar, but somewhere further down (assuming there is more code following what was in the original posting), there are statements that assign the value in column Hr to another column that is of type INT. That is what DonAtWork was trying to suggest.

DeanT
Starting Member

13 Posts

Posted - 2012-02-29 : 12:00:35

Sorry guys for the protection of my company I've not posted all the code yet

Here is more of the code, and I will guarantee you that the Hr column is a varchar

create table #hrtable (
Hr varchar(25),
Hr_term varchar(512),
calls int,
avg_call_duration varchar(20),
total_call_duration varchar(20),
max_call_duration varchar(40),
min_call_duration varchar(40),
avg_calls_per_hour numeric(4,2)
)

the table was not really dbo.Customer it was #hrtable

I found a work around instead of trying the "in place" update of the Hr column I created a new column in the temp table called Hr_temp and updated that column and it worked like a champ but I really need to "overwrite" the Hr column with the text is that not possible?

quote:
Originally posted by DeanT

I have been working on trying to figure out why I cannot update a varchar field in place with this statement:

UPDATE dbo.Customer
SET Hr = CASE
WHEN Hr = '-6' THEN '12:00 am - 1:00 am'
WHEN Hr = '-5' THEN '1:00 am - 2:00 am'
WHEN Hr = '-4' THEN '2:00 am - 3:00 am'
WHEN Hr = '-3' THEN '3:00 am - 4:00 am'
WHEN Hr = '-2' THEN '4:00 am - 5:00 am'
WHEN Hr = '-1' THEN '5:00 am - 6:00 am'
WHEN Hr = '0' THEN '6:00 am - 7:00 am'
WHEN Hr = '1' THEN '7:00 am - 8:00 am'
WHEN Hr = '2' THEN '8:00 am - 9:00 am'
WHEN Hr = '3' THEN '9:00 am - 10:00 am'
WHEN Hr = '4' THEN '10:00 am - 11:00 am'
WHEN Hr = '5' THEN '11:00 am - 12:00 pm'
WHEN Hr = '6' THEN '12:00 pm - 1:00 pm'
WHEN Hr = '7' THEN '1:00 pm - 2:00 pm'
WHEN Hr = '8' THEN '2:00 pm - 3:00 pm'
WHEN Hr = '9' THEN '3:00 pm - 4:00 pm'
WHEN Hr = '10' THEN '4:00 pm - 5:00 pm'
WHEN Hr = '11' THEN '5:00 pm - 6:00 pm'
WHEN Hr = '12' THEN '6:00 pm - 7:00 pm'
WHEN Hr = '13' THEN '1:00 pm - 2:00 pm'
WHEN Hr = '14' THEN '8:00 pm - 9:00 pm'
WHEN Hr = '15' THEN '9:00 pm - 10:00 pm'
WHEN Hr = '16' THEN '10:00 pm - 11:00 pm'
WHEN Hr = '17' THEN '11:00 pm - 12:00 pm'
ELSE NULL
END

Hr is defined as a varchar(512) field but I'm getting an error:

Msg 245, Level 16, State 1, Line 69 Conversion failed when converting the varchar value '12:00 am - 1:00 am' to data type int.

http://www.local-developers.com

See me at
http://www.local-developers.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-29 : 12:03:58

yep...thats a much better approach instead of having series of CASE condition. I would even add a range table for storing hour ranges with min and max variable to make it flexible and scalable so that I can add/modify ranges based on business need

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DeanT
Starting Member

13 Posts

Posted - 2012-02-29 : 12:06:23

Sorry again, here is more code: (comes after the create table #hrtable)

insert into #hrtable (Hr, calls, avg_call_duration, total_call_duration, max_call_duration, min_call_duration)
(SELECT DATEPART(HOUR, startdatetime)-6, COUNT(distinct(sessionid)),
right('0' + rtrim(convert(char(2), (sum(connecttime)/COUNT(distinct(sessionid))) / (60 * 60))), 2) + ':' +
right('0' + rtrim(convert(char(2), (sum(connecttime)/COUNT(distinct(sessionid)) / 60) % 60)), 2) + ':' +
right('0' + rtrim(convert(char(2), (sum(connecttime)/COUNT(distinct(sessionid))) % 60)),2),
--SUM(connecttime),

right('0' + rtrim(convert(char(2), (sum(connecttime)) / (60 * 60))), 2) + ':' +
right('0' + rtrim(convert(char(2), (sum(connecttime) / 60) % 60)), 2) + ':' +
right('0' + rtrim(convert(char(2), (sum(connecttime)) % 60)),2),

right('0' + rtrim(convert(char(2), (max(connecttime)) / (60 * 60))), 2) + ':' +
right('0' + rtrim(convert(char(2), (max(connecttime) / 60) % 60)), 2) + ':' +
right('0' + rtrim(convert(char(2), (max(connecttime)) % 60)),2),

right('0' + rtrim(convert(char(2), (min(connecttime)) / (60 * 60))), 2) + ':' +
right('0' + rtrim(convert(char(2), (min(connecttime) / 60) % 60)), 2) + ':' +
right('0' + rtrim(convert(char(2), (min(connecttime)) % 60)),2)
FROM contactcalldetail
WHERE (startdatetime BETWEEN DATEADD(hour, 6, @startdate) AND DATEADD(hour, 6, @enddate)) and contacttype = 1
GROUP BY DATEPART(HOUR, startdatetime))

See me at
http://www.local-developers.com

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 12:10:31

T^his is the only way to get that error



create table dbo.Customer 
--(hr varchar(512))
(hr int)
insert into Customer (hr)
values ('-2'),('-4'),('3'),('7'),('14')

select * from Customer

UPDATE dbo.Customer
SET Hr = CASE
WHEN Hr = '-6' THEN '12:00 am - 1:00 am'
WHEN Hr = '-5' THEN '1:00 am - 2:00 am'
WHEN Hr = '-4' THEN '2:00 am - 3:00 am'
WHEN Hr = '-3' THEN '3:00 am - 4:00 am'
WHEN Hr = '-2' THEN '4:00 am - 5:00 am'
WHEN Hr = '-1' THEN '5:00 am - 6:00 am'
WHEN Hr = '0' THEN '6:00 am - 7:00 am'
WHEN Hr = '1' THEN '7:00 am - 8:00 am'
WHEN Hr = '2' THEN '8:00 am - 9:00 am'
WHEN Hr = '3' THEN '9:00 am - 10:00 am'
WHEN Hr = '4' THEN '10:00 am - 11:00 am'
WHEN Hr = '5' THEN '11:00 am - 12:00 pm'
WHEN Hr = '6' THEN '12:00 pm - 1:00 pm'
WHEN Hr = '7' THEN '1:00 pm - 2:00 pm'
WHEN Hr = '8' THEN '2:00 pm - 3:00 pm'
WHEN Hr = '9' THEN '3:00 pm - 4:00 pm'
WHEN Hr = '10' THEN '4:00 pm - 5:00 pm'
WHEN Hr = '11' THEN '5:00 pm - 6:00 pm'
WHEN Hr = '12' THEN '6:00 pm - 7:00 pm'
WHEN Hr = '13' THEN '1:00 pm - 2:00 pm'
WHEN Hr = '14' THEN '8:00 pm - 9:00 pm'
WHEN Hr = '15' THEN '9:00 pm - 10:00 pm'
WHEN Hr = '16' THEN '10:00 pm - 11:00 pm'
WHEN Hr = '17' THEN '11:00 pm - 12:00 pm'
ELSE NULL
END

-- Msg 245, Level 16, State 1, Line 1
-- Conversion failed when converting the varchar value '4:00 am - 5:00 am' to data type int.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources