|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-06-11 : 08:17:29
|
Lynn writes "Greetings,
I am having a problem converting an array from hours and minutes (such as 3:20) to just minutes. I am passed an employee id, a weekending date and then a string of values that starts with a project id and then a value for each day of the week. The table stores values in minutes, but I can't figure out how to convert them. I've attached the code and I wrote it (I thought) so that it would take the string, break it up, convert the hour:minute value (varchar) into a minute (smallint) and then insert or update into a table. Can you help me figure out how come it will not convert from varchar into smallint?
Much thanks,
Lynn
CREATE PROCEDURE SP_INSERT_TIMESHEETDETAILS
@EMPLOYEEID VARCHAR(10),
@WEEKEND SMALLDATETIME,
@PROJECTIME_str varchar(2000)
AS
set nocount on
declare @separator_position int -- This is used to locate each separator character
declare @array_value decimal(4,2) -- this holds each array value as it is returned
declare @separator char(1) -- this is delimiter
/*local variables*/
DECLARE @L_VPROJECTID bigint
DECLARE @L_VSAT SMALLINT
DECLARE @L_VSUN SMALLINT
DECLARE @L_VMON SMALLINT
DECLARE @L_VTUES SMALLINT
DECLARE @L_VWED SMALLINT
DECLARE @L_VTHUR SMALLINT
DECLARE @L_VFRI SMALLINT
DECLARE @L_VCOUNT INT
DECLARE @L_VTIMESHEETID VARCHAR(10)
set @separator = ',' --comma delimiter
set @PROJECTIME_str = @PROJECTIME_str + @separator
set @l_VCount = 1
SELECT REPLACE(@PROJECTIME_STR, ':', '.')
-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @PROJECTIME_str) <> 0
begin
-- patindex matches the pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @PROJECTIME_str)
SELECT @array_value = left(@PROJECTIME_str, @separator_position - 1)
IF RIGHT(@ARRAY_VALUE, 2) = 15 SET @ARRAY_VALUE = CAST((REPLACE(@ARRAY_VALUE, 15, .25) *60) AS SMALLINT)
IF RIGHT(@ARRAY_VALUE, 2) = 30 SET @ARRAY_VALUE =CAST((CAST(REPLACE(@ARRAY_VALUE, 30, 50) AS DECIMAL(4,3) ))*60 AS INT)
IF RIGHT(@ARRAY_VALUE, 2) = 45 SET @ARRAY_VALUE = CAST((CAST(REPLACE(@ARRAY_VALUE, 45, 75) AS DECIMAL(4,3) ))*60 AS INT) --CHANGED BY REMOVING QUOTES
-- SELECT CAST((@ARRAY_VALUE*60) AS DECIMAL(4,3))
BEGIN
if @l_VCount = 1 SET @L_VPROJECTID = @array_value
if @l_VCount = 2 SET @L_VSAT = @array_value
if @l_VCount = 3 SET @L_VSUN =@array_value
if @l_VCount = 4 SET @L_VMON =@array_value
if @l_VCount = 5 SET @L_VTUES = @array_value
if @l_VCount = 6 SET @L_VWED =@array_value
if @l_VCount = 7 SET @L_VTHUR = @array_value
if @l_VCount = 8 SET @L_VFRI = @array_value
IF @L_VCOUNT = 9 SET @L_VTIMESHEETID = @ARRAY_VALUE
-- This replaces what we just processed with an empty string
select @PROJECTIME_str = stuff(@PROJECTIME_str, 1, @separator_position, '')
SET @l_VCount = @l_VCount+1
END
end
IF @L_VTIMESHEETID=0
BEGIN
BEGIN TRANSACTION Ins
INSERT INTO TIMESHEET
(EMPLOYEEID, WEEKENDINGDATE) VALUES (@EMPLOYEEID, @WEEKEND)
SET @L_VTIMESHEETID = (SELECT TIMESHEETID FROM TIMESHEET WHERE EMPLOYEEID = @EMPLOYEEID AND WEEKENDINGDATE = @WEEKEND)
Insert into DETAILS2
(TIMESHEETID, PROJECTID, SATHOURS, SUNHOURS, MONHOURS, TUESHOURS, WEDHOURS, THURHOURS, FRIHOURS)
values
(@L_VTIMESHEETID, @L_VPROJECTID, @L_VSAT, @L_VSUN, @L_VMON, @L_VTUES, @L_VWED, @L_VTHUR, @L_VFRI)
COMMIT TRANSACTION INS
END
IF @L_VTIMESHEETID>0
BEGIN
BEGIN TRANSACTION UPD
UPDATE DETAILS2
SET DETAILS2.PROJECTID = @L_VPROJECTID, DETAILS2.SATHOURS = @L_VSAT, DETAILS2.SUNHOURS = @L_VSUN, DETAILS2.MONHOURS = @L_VMON, DETAILS2.TUESHOURS = @L_VTUES,
DETAILS2. |
|
Converting a time value (varchar) to minutes (smallint) in an array