November 21, 2005 at 10:45 am
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
November 21, 2005 at 10:55 am
SET NOCOUNT ON
GO
DECLARE @myTable TABLE
(
myID INT,
srawlogin VARCHAR(25),
srawlogout VARCHAR(25) NULL,
CallTime INT NULL
)
INSERT @myTable (myID, srawlogin) VALUES (1, '11:45:21')
DECLARE @srawlogout VARCHAR(25)
SET @srawlogout = '11:45:55'
UPDATE @myTable
SET
srawlogout = @srawlogout,
CallTime = DATEDIFF(SECOND, CONVERT(DATETIME, srawlogin), CONVERT(DATETIME, @srawlogout))
WHERE
myID = 1
SELECT * FROM @myTable
Regards,
gova
November 21, 2005 at 10:57 am
But I would want to convert the datatypes of login and logout as DATETIME since if the data is like below it would give a negative time.
SET NOCOUNT ON
GO
DECLARE @myTable TABLE
(
myID INT,
srawlogin VARCHAR(25),
srawlogout VARCHAR(25) NULL,
CallTime INT NULL
)
INSERT @myTable (myID, srawlogin) VALUES (1, '23:45:21')
DECLARE @srawlogout VARCHAR(25)
SET @srawlogout = '00:45:55'
UPDATE @myTable
SET
srawlogout = @srawlogout,
CallTime = DATEDIFF(SECOND, CONVERT(DATETIME, srawlogin), CONVERT(DATETIME, @srawlogout))
WHERE
myID = 1
SELECT * FROM @myTable
Regards,
gova
November 21, 2005 at 11:38 am
Thank you...this works, but I need the calltime to be in the same format as the login and logout...00:00:34
How can I do this?
November 21, 2005 at 1:34 pm
DO NOT CONVERT DATETIME TO VARCHAR!
DO NOT CONVERT DATETIME TO VARCHAR!!!
DO NOT CONVERT DATETIME TO VARCHAR!!!!!
CallTime = srawlogout - srawlogin
That's it.
If you need inly time portion of a datetime value, use this:
@TimeOnly = @DateTime - convert(@Datetime, FLOOR(convert(float, @Datetime)))
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply