May 12, 2017 at 1:37 am
In my table I have a string column that contains a string representation of a time. The field could contains values such as
2300
1100
1900
1300
so ive used some code to convert the string to a time, but its failing and i cant see why. This is the particular line in my statememnt thats failing
WHEN EndTime LIKE '[0-9][0-9][0-9][0-9]' THEN CONVERT(time, LEFT(EndTime,2) + ':' + RIGHT(EndTime,2) + ':00', 108)
none of the columns that contain 4 character times like 2300, 1300,1900, etc will convert, yet I know the method works because Ive used it on a diferent field that contains the same type of data (starttime)
ive checked the content length and its 4, but I just cant see what the problem is, can anyone see where im going wrong ?
May 12, 2017 at 2:09 am
Ive also just tried this function that should remove any CR LF Tab, spaces, etc, but that made no difference, this has really got me stumped
CREATE function [dbo].[RemoveNonNumericChar](@str varchar(500))
returns varchar(500)
begin
declare @startingIndex int
set @startingIndex=0
while 1=1
begin
set @startingIndex= patindex('%[^0-9]%',@str)
if @startingIndex <> 0
begin
set @STR = replace(@str,substring(@str,@startingIndex,1),'')
end
else break;
end
SET @STR =REPLACE(REPLACE(REPLACE(@str , CHAR(9), ''), CHAR(10), ''), CHAR(13), '')
return @STR
end
May 12, 2017 at 2:11 am
What's the actual data type of the EndTime field?
I've done some testing (on SQL2012 & 2014), and have noticed that if it's a VARCHAR(x) then you're fine, but if it's a CHAR(x) field then you get errors, as this shows:
DECLARE @test-2 TABLE ( EndTime CHAR(10)) ;
INSERT INTO @test-2
VALUES ( '2300' ) , ( '1900' ) , ( 'abc' ) , ( '123' ) , ( '13:37' ) ;
SELECT
EndTime , LEN(EndTime) AS [Len], DATALENGTH(EndTime) AS [DataLength], LEFT(endtime,2)+':'+RIGHT(endtime,2)+':00' AS [ToConvert]
--,CASE
-- WHEN EndTime LIKE '[0-9][0-9][0-9][0-9]'
-- THEN CONVERT(TIME, LEFT(EndTime, 2) + ':' + RIGHT(EndTime, 2) + ':00', 108)
-- ELSE
-- NULL
--END
FROM
@test-2 ;
And the results of that give the last field (the value that would then be passed into the CONVERT() function
+=========+=====+============+===========+
| EndTime | Len | DataLength | ToConvert |
+=========+=====+============+===========+
| 2300 | 4 | 10 | 23: :00 |
+---------+-----+------------+-----------+
| 1900 | 4 | 10 | 19: :00 |
+---------+-----+------------+-----------+
| abc | 3 | 10 | ab: :00 |
+---------+-----+------------+-----------+
| 123 | 3 | 10 | 12: :00 |
+---------+-----+------------+-----------+
| 13:37 | 5 | 10 | 13: :00 |
+---------+-----+------------+-----------+
Spot what's wrong? (Besides the obvious pants data formatting...)
Thomas Rushton
blog: https://thelonedba.wordpress.com
May 12, 2017 at 2:14 am
Storing times in a varchar isn't is the best of ideas. SQL 2012 onwards has a time datatype, I would suggest making use of this (it'll save you hassle like this in the fiuture).
I'm going to guess that possibly you have other values in this column that are not times? This should work for you:USE DevTestDB;
GO
CREATE TABLE #Sample (StringTime char(40));
GO
INSERT INTO #Sample
VALUES
('2300'),
('1100'),
('1900'),
('1300'),
('not a time'),
('some other value'),
('135764');
GO
SELECT *,
TRY_CAST(STUFF(StringTime,3,0,':') AS time) AS TimeValue
FROM #Sample;
GO
DROP TABLE #Sample;
GO
Edit: Just o note, this works on both char and varchar (the test SQL actually uses char(40) to check if I had Thomas' problem).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 12, 2017 at 2:20 am
solus - Friday, May 12, 2017 2:09 AMIve also just tried this function that should remove any CR LF Tab, spaces, etc, but that made no difference, this has really got me stumped
CREATE function [dbo].[RemoveNonNumericChar](@str varchar(500))
returns varchar(500)
begin
declare @startingIndex int
set @startingIndex=0
while 1=1
begin
set @startingIndex= patindex('%[^0-9]%',@str)
if @startingIndex <> 0
begin
set @STR = replace(@str,substring(@str,@startingIndex,1),'')
end
else break;
end
SET @STR =REPLACE(REPLACE(REPLACE(@str , CHAR(9), ''), CHAR(10), ''), CHAR(13), '')
return @STR
end
That is going to be terribly slow. It's a scalar function (which aren't that quick, TVF's are better), and it's iterative.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 12, 2017 at 2:26 am
Thom A - Friday, May 12, 2017 2:14 AM
Edit: Just o note, this works on both char and varchar (the test SQL actually uses char(40) to check if I had Thomas' problem).
Not *my* problem - I was just trying to show why the problem would occur if you used CHAR field and the LEFT/RIGHT method that the OP was using... 🙂
+1 for STUFF() and TRY_CONVERT - nice touch.
Thomas Rushton
blog: https://thelonedba.wordpress.com
May 12, 2017 at 2:31 am
ThomasRushton - Friday, May 12, 2017 2:26 AMThom A - Friday, May 12, 2017 2:14 AM
Edit: Just o note, this works on both char and varchar (the test SQL actually uses char(40) to check if I had Thomas' problem).Not *my* problem - I was just trying to show why the problem would occur if you used CHAR field and the LEFT/RIGHT method that the OP was using... 🙂
+1 for STUFF() and TRY_CONVERT - nice touch.
Haha, true; perhaps "The problem Thomas displayed if you are using a char datatype with LEFT RIGHT operators." 😉
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 12, 2017 at 2:32 am
Ive tried this
WHEN EndTime LIKE '[0-9][0-9][0-9][0-9]' THEN LEFT(dbo.RemoveNonNumericChar(EndTime),2) + ':' + RIGHT(dbo.RemoveNonNumericChar(EndTime),2) + ':00.0000000'
and this gives me a string (and ALL the other values give me the same format as a string)
11:00:00.0000000
19:00:00.0000000
23:00:00.0000000
13:00:00.0000000
but when I modify the above code to this
WHEN EndTime LIKE '[0-9][0-9][0-9][0-9]' THEN CONVERT(time,LEFT(dbo.RemoveNonNumericChar(EndTime),2) + ':' + RIGHT(dbo.RemoveNonNumericChar(EndTime),2) + ':00.0000000', 108)
I get an error
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
what the heck could be causing this ?
May 12, 2017 at 2:33 am
the underlying field is varchar(50)
May 12, 2017 at 2:35 am
A thought about the 'LIKE' clause you have...
If you know that your data is going to be hhmm, then you shouldn't be looking for items
WHERE EndTime LIKE '[0-9][0-9][0-9][0-9]'
as that will also match things like "9999" which is not a valid time. On this planet, anyway.
Try
WHERE EndTime LIKE '[0-2][0-9][0-5][0-9]'
to at least restrict it to digits that could be involved in a time.
Or, do it really properly, and check EndTime more thoroughly:
EndTime LIKE '[0-2][0-3][0-5][0-9]' OR EndTime LIKE '[0-1][0-9][0-5][0-9]'
Thomas Rushton
blog: https://thelonedba.wordpress.com
May 12, 2017 at 2:36 am
solus - Friday, May 12, 2017 2:32 AMIve tried thisWHEN EndTime LIKE '[0-9][0-9][0-9][0-9]' THEN LEFT(dbo.RemoveNonNumericChar(EndTime),2) + ':' + RIGHT(dbo.RemoveNonNumericChar(EndTime),2) + ':00.0000000'
and this gives me a string (and ALL the other values give me the same format as a string)
11:00:00.0000000
19:00:00.0000000
23:00:00.0000000
13:00:00.0000000but when I modify the above code to this
WHEN EndTime LIKE '[0-9][0-9][0-9][0-9]' THEN CONVERT(time,LEFT(dbo.RemoveNonNumericChar(EndTime),2) + ':' + RIGHT(dbo.RemoveNonNumericChar(EndTime),2) + ':00.0000000', 108)
I get an error
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.what the heck could be causing this ?
Why the heck haven't you tried any of the suggestions above? 🙂
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 12, 2017 at 2:36 am
solus - Friday, May 12, 2017 2:32 AMIve tried thisWHEN EndTime LIKE '[0-9][0-9][0-9][0-9]' THEN LEFT(dbo.RemoveNonNumericChar(EndTime),2) + ':' + RIGHT(dbo.RemoveNonNumericChar(EndTime),2) + ':00.0000000'
and this gives me a string (and ALL the other values give me the same format as a string)
11:00:00.0000000
19:00:00.0000000
23:00:00.0000000
13:00:00.0000000but when I modify the above code to this
WHEN EndTime LIKE '[0-9][0-9][0-9][0-9]' THEN CONVERT(time,LEFT(dbo.RemoveNonNumericChar(EndTime),2) + ':' + RIGHT(dbo.RemoveNonNumericChar(EndTime),2) + ':00.0000000', 108)
I get an error
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.what the heck could be causing this ?
have you got any "numbers" that cannot be converted to time...ie 2516 or 1061?
EDIT...seems I was beaten to it 😀😀
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 12, 2017 at 2:36 am
ok, cool Ill change it to a tvf, I just wanted something quick and dirty while im testing and developing this bit of functionality, but thanks for the heads up about performance 🙂
May 12, 2017 at 2:38 am
solus - Friday, May 12, 2017 2:36 AMok, cool Ill change it to a tvf, I just wanted something quick and dirty while im testing and developing this bit of functionality, but thanks for the heads up about performance 🙂
Why... What's wrong with my TRY_CAST & STUFF answer, or Thomas' answer (which should work as you're using a varchar, not char)?
Even if you change it to a TVF it's still iterative, so the performance won't be as good as it could be.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 12, 2017 at 2:39 am
these are all the string values in the table, none of them are out of time conversion range
11:00:00.0000000
19:00:00.0000000
23:00:00.0000000
13:00:00.0000000
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply