Update Question - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Update Question

Author

Topic

adz2013
Starting Member

6 Posts

Posted - 2004-11-08 : 09:02:43

I am somewhat new at this and I am stuck on this one issue. I have three fields (WOMONTH, WOYEAR, WODAY) that are varchar(2), which I extracted out of another field. I am now trying to put them togeather as a date in a fourth field. I can have that field as a datetime or as a varchar(8), which ever way works best. Here is how I wrote my statement:

UPDATE [Local Service Work Order Information]
SET ORDERDATE = WOMONTH & '/' & WODAY & '/' & WOYEAR

and this is the error messeage that I am getting:

Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals boolean AND, type equals varchar.

Any help would be appreciated.

Thank you.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-08 : 09:23:50

SET ORDERDATE = WOMONTH + '/' + WODAY + '/' + WOYEAR

but better as
SET ORDERDATE = WOYEAR + WOMONTH + WODAY

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

adz2013
Starting Member

6 Posts

Posted - 2004-11-08 : 09:27:37

Thank you for your response but when I did SET ORDERDATE = WOMONTH + '/' + WODAY + '/' + WOYEAR or SET ORDERDATE = WOYEAR + WOMONTH + WODAY I received this message.

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

I have the field set as a varchar(8)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-08 : 11:44:31

Didn't notice your datatypes - but you shouldn't get that error if what you say is true - the two queries will give a max of 8 or 6 chars.

If you want to save it in a varchar then I would save as yyyymmdd so that you can do comparisons and also won't have problems with regional dates.
You will have to decide which century the date is in to do that
case when convert(int,WOYEAR) < 50 then '20' else '19' end + WOYEAR + WOMONTH + WODAY

Doesn't solve the immediate problem which as I say can't happen with the info you have given.

try
select max(datalength(WOYEAR + WOMONTH + WODAY))
from tbl
and
select top 10 WOYEAR, WOMONTH, WODAY
from tbl
where len(WOYEAR + WOMONTH + WODAY) = (select max(datalength(WOYEAR + WOMONTH + WODAY)) from tbl)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

adz2013
Starting Member

6 Posts

Posted - 2004-11-08 : 13:15:33

Thank you very much, I had to change some data types but it worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources