Update Error --Using the Convert function

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 Error --Using the Convert function

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2005-11-21 : 12:23:45

I have a varchar field called calltime that contains data that looks like this "03:34:55". I have other fields called login and logout. The calltime field is calculated by subtracting the login field from the logout field.

So if I have login "11:45:21" and logout "11:45:55", the calltime should be "00:00:34".

I use the following query:


UPDATE TimeTable
SET  CallTime = convert(varchar(20), convert(datetime, srawlogout) - convert(datetime, srawlogin),108)

When I use this in a select statement it works, but when I try to update I get the following error:
"Syntax error converting datetime from character string."

Can someone please help me...What am I doing wrong???

Thanks,
Ninel

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-21 : 12:28:19

Maybe if you post the DDL, but I suspect that your logins times are varchar, and you have rows that have invalid time data What does

SELECT * FROM timetable WHERE ISDATE(srwalogout)=0 OR ISDATE(srwalogin)=0

Give you?

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

Add yourself!
http://www.frappr.com/sqlteam

ninel
Posting Yak Master

141 Posts

Posted - 2005-11-21 : 13:15:09

My login times are varchar. The select statement returns all the records.

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-21 : 13:23:30

That means that sql server doesn't think they are dates, hence your error.

Post some sample data, and the DDL of the table

Read the hint link in my sig to see examples of what we need.

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

Add yourself!
http://www.frappr.com/sqlteam

ninel
Posting Yak Master

141 Posts

Posted - 2005-11-21 : 13:37:03

This is what I have:


CREATE TABLE #tmPunchTime (
	[iTmPunchTimeId] [int] IDENTITY (1, 1) NOT NULL ,
	[sCalldate] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[sAgentid] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[sRawLogin] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[sRawLogout] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[sCallTime] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[dCallSec] [decimal](18, 0) NULL 
) ON [PRIMARY]
GO

INSERT INTO #tmPunchTime (sCalldate, sAgentid, sRawLogin, sRawLogout)
VALUES ('20051121','123','11:45:55', '11:45:21')

SELECT * FROM #tmPunchTime

UPDATE #tmPunchTime
SET sCallTime =  convert(varchar(25), (CONVERT(DATETIME,srawlogin ) - CONVERT(DATETIME, srawlogout)), 108)

The weird thing is that this code works. It updated correctly, but the table in the database is giving me errors. Could the table be corrupted?

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-21 : 14:00:35

Well, you said that this code

SELECT * FROM #tmPunchTime WHERE ISDATE(srawlogout)=0 OR ISDATE(srawlogin)=0

Returns all the rows in your table, but if you run it against your example, you will see it returns mo rows.

Is this data coming from the mainframe or AS400?

My guess is that your time value columns have some unprintable data

but you can still check for corruption with DBCC CHECKTABLE tablename

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

Add yourself!
http://www.frappr.com/sqlteam

ninel
Posting Yak Master

141 Posts

Posted - 2005-11-21 : 14:43:36

There was a login entry in the database that contained a semicolon instead of a colon, which is why I was receiving the error. .....ARGH sometimes I just want to strangle the users ...

Thank you for your help.

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-21 : 15:05:07

The ISDATE() query I gave you should have shown you that row....you sure it returned all the rows?

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

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-22 : 01:50:06

Why did you use Varchar Data type to store Dates and Times?
Use DateTime Datatype so that you can avoid this type of conversions

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources