May 19, 2017 at 3:33 am
In my table there are string representations of time, eg
'2300'
'1900'
'2100'
etc
so I have some inline code to convert to an actual time value[tmptime=
CASE
WHEN StartTime LIKE '[0-9][0-9][0-9]' THEN CONVERT(time, '0' + LEFT(StartTime,1) + ':' + RIGHT(StartTime,2) + ':00', 108)
WHEN StartTime LIKE '[0-9][0-9]:[0-9][0-9] AM' OR StartTime LIKE '[0-9][0-9]:[0-9][0-9] PM' THEN CONVERT(time, StartTime, 108)
WHEN StartTime = '0' THEN CONVERT (time, '00:00:00.0000000')
WHEN StartTime LIKE '%' + '2400' + '%' THEN CONVERT (time, '23:59:59.0000000')
WHEN StartTime LIKE '[0-9][0-9][0-9][0-9]' THEN TRY_PARSE(STUFF(StartTime,3,0,':') AS time using 'en-GB')
WHEN StartTime = '30' THEN CONVERT (time, '00:30:00.0000000')
END,]
I decided to convert this into a scalar valued function, like so
[CREATE FUNCTION [dbo].[ConvertStringToTime]
(
@T VarChar
)
returns time
AS
BEGIN
-- Declare the return variable here
DECLARE @ReturnTime time
SET @ReturnTime =
CASE
WHEN @T LIKE '[0-9][0-9][0-9]' THEN CONVERT(time, '0' + LEFT(@T,1) + ':' + RIGHT(@T,2) + ':00', 108)
WHEN @T LIKE '[0-9][0-9]:[0-9][0-9] AM' OR @T LIKE '[0-9][0-9]:[0-9][0-9] PM' THEN CONVERT(time, @T, 108)
WHEN @T LIKE '[0]' THEN CONVERT (time, '00:00:01.0000000')
WHEN @T LIKE '%' + '2400' + '%' THEN CONVERT (time, '23:59:59.0000000')
WHEN @T LIKE '[0-9][0-9][0-9][0-9]' THEN TRY_PARSE(STUFF(@T,3,0,':') AS time using 'en-GB')
WHEN @T = '30' THEN CONVERT (time, '00:30:00.0000000')
ELSE NULL
END
return @ReturnTime
END
]
the inline sql works fine, I get a time value from the string, but when I do this
[DerivedStartTime = dbo.ConvertStringToTime(StartTime)]
The value returned is NULL
I cant see why, the code is exactly the same. Can anyone tell me where Im going wrong here ?
May 19, 2017 at 4:53 am
the literal values need to have a colon separator for hours and minutes. You dont seem to have this in all of the comparisons
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 19, 2017 at 5:00 am
there are no colons in the starttime the values will always be like this
1300
900
1100
2030
0
700
etc,etc
May 19, 2017 at 5:00 am
it makes no sense why this code works perfectly fine if its inline, but it wont work inside a function
May 19, 2017 at 5:13 am
You've declared @T as a varchar(1)(
@T VarChar
)
Edit:
This means most of your values will be truncated:'1300' -> '1'
'900' -> '9'
'1100' -> '1'
'2030' -> '2'
'0' -> '0'
'700' -> '7'
ALWAYS declare the length of your data types. ALWAYS.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 19, 2017 at 5:32 am
Ive changed the varchar size
ALTER FUNCTION [dbo].[ConvertStringToTime]
(
@T VarChar(4)
)
but im still getting null returned
May 19, 2017 at 5:34 am
May 19, 2017 at 5:37 am
DOH ! im an idiot, Id created a temp variable inside the function and trimmed any leading or trailing spaces from it, but id forgot to set the variables length, now its set to 4 and the function works, thank you so much for pointing that schoolboy error out
May 19, 2017 at 5:39 am
solus - Friday, May 19, 2017 5:32 AMIve changed the varchar sizeALTER FUNCTION [dbo].[ConvertStringToTime]
(
@T VarChar(4)
)but im still getting null returned
For what values? Doing that amend fixed the problem for me, and tested on our SQL 2012 server.
Sample SQL:USE DevTestDB;
GO
CREATE FUNCTION [dbo].[ConvertStringToTime] (@T VarChar(4))
RETURNS TIME AS
BEGIN
-- Declare the return variable here
DECLARE @ReturnTime time
SET @ReturnTime =
CASE WHEN @T LIKE '[0-9][0-9][0-9]' THEN CONVERT(time, '0' + LEFT(@T,1) + ':' + RIGHT(@T,2) + ':00', 108)
WHEN @T LIKE '[0-9][0-9]:[0-9][0-9] AM' OR @T LIKE '[0-9][0-9]:[0-9][0-9] PM' THEN CONVERT(time, @T, 108)
WHEN @T LIKE '[0]' THEN CONVERT (time, '00:00:01.0000000')
WHEN @T LIKE '%' + '2400' + '%' THEN CONVERT (time, '23:59:59.0000000')
WHEN @T LIKE '[0-9][0-9][0-9][0-9]' THEN TRY_PARSE(STUFF(@T,3,0,':') AS time using 'en-GB')
WHEN @T = '30' THEN CONVERT (time, '00:30:00.0000000')
ELSE NULL
END;
RETURN @ReturnTime;
END
GO
CREATE TABLE #Sample (T varchar(4));
GO
INSERT INTO #Sample
VALUES
('1300'),
('900'),
('1100'),
('2030'),
('0'),
('700');
GO
SELECT *, dbo.ConvertStringToTime(T) AS Converted
FROM #Sample;
GO
DROP TABLE #Sample;
DROP FUNCTION dbo.ConvertStringToTime;
GO
Returns:T Converted
---- ----------------
1300 13:00:00.0000000
900 09:00:00.0000000
1100 11:00:00.0000000
2030 20:30:00.0000000
0 00:00:01.0000000
700 07:00:00.0000000
What value doesn't work for you?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 19, 2017 at 5:44 am
solus - Friday, May 19, 2017 5:37 AMDOH ! im an idiot, Id created a temp variable inside the function and trimmed any leading or trailing spaces from it, but id forgot to set the variables length, now its set to 4 and the function works, thank you so much for pointing that schoolboy error out
This is why it's also important to provide us with your full SQL, and not a part of it. 😉
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 19, 2017 at 6:16 am
To be honest, using a scalar function here is a bit insane not to mention how complex it's been made. Like Perry Whittle stated, you need the colons. The only way to do that right is to ensure that each time literal is 4 characters long so you can STUFF a colon into the correct position. And you should NOT need to test for "2400" because if the times were actually generated correctly, there is no such thing as "24:00". It becomes "00:00" of the next day.
Here's some example code. Next time, please post readily consumable code to build the test data as I have done below. Please see the first link under "Helpful Links" in my signature line below for tips on how to write a post that will get you better answers more quickly.
--===== Create a table of test data.
-- This is NOT a part of the solution.
-- We're just building some test data here.
CREATE TABLE #TestTable
(SomeTime VARCHAR(4))
;
INSERT INTO #TestTable
(SomeTime)
SELECT SomeTime
FROM (
SELECT '1300' UNION ALL
SELECT '900' UNION ALL
SELECT '1100' UNION ALL
SELECT '2030' UNION ALL
SELECT '0' UNION ALL
SELECT '700'
) d (SomeTime)
;
--===== This is much simpler than what you have.
-- It ensures the correct length to greatly simplify the code.
-- Like the man said, you're missing the colons and this fixes that.
SELECT Original = SomeTime
,Converted = CAST(STUFF(RIGHT('0000'+SomeTime,4),3,0,':') AS TIME)
FROM #TestTable
;
Here are the results of the code above...
Original Converted
-------- ----------------
1300 13:00:00.0000000
900 09:00:00.0000000
1100 11:00:00.0000000
2030 20:30:00.0000000
0 00:00:00.0000000
700 07:00:00.0000000
(6 row(s) affected)
p.s. I'm also not sure why you're converting a "0" to 1 second, either. Seems equally insane because you're changing the original data instead of converting it faithfully. It'll bite you some how, some way, some time in the future.. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply