Sql Server 2008 Conversion from string to time

Question

Hi to all,
i have a strange behaviuor converting a column to time.
I have an nvarchar column with a date in this format: dd/MM/yyyy hh:mm:ss and i'm trying to convert it to type TIME but i have an error on conversion.
My query is

select top 1
''''+right(MESSAGE_DATE,8)+'''',
''''+LTRIM(RTRIM(SUBSTRING(MESSAGE_DATE,12, 9)))+':000'+'''',
--CONVERT(DATETIME,right(MESSAGE_DATE,8),8) ,
--CONVERT(TIME,LTRIM(RTRIM(SUBSTRING(MESSAGE_DATE,12, 9)))+':000',114),
--CONVERT(TIME,CAST(right(MESSAGE_DATE,8) as NVARCHAR(8)),8)
FROM TABLE

I use the right() and substring() func to get the portion of string with time, but every style conversion i tried it give to me the same error:
The input character string does not follow style 114, either change the input character string or use a different style.

i tried to do this test: SELECT CONVERT(TIME,'23:52:25:000',114),CONVERT(DATETIME,'23:52:25:000',114) and i was able to get the correct time without errors.
Can anyone help me to solve this issue?
Best Regards
Sergio

Answer 1

Thank you all for the answers, at the end i decide to use full datetime and manage time at application level.. it is not what i want but it works.
Thank you again for the time spent :)

Answer 2

Show the value that does not work and try this test:

select cast(substring(ltrim(' 01/03/2020 14:15:16some other text '), 12, 8) as time)

Answer 3

Thank you for the answer.
All the values in that column give me the error, for example
30/04/2019 23:52:25
13/11/2017 23:29:00
22/07/2012 23.58.23
29/08/2011 23.58.02
I tried your suggestion but it give me the same error.
I think there is something wrong with the string written, or how it is written, but i don't know what i can check, the type is varchar(20)... what else i can check?
It make no sense to me

Answer 4

DECLARE @T TABLE (
    MESSAGE_DATE varchar(20)
);
INSERT INTO @T VALUES
('30/04/2019 23:52:25'), ('13/11/2017 23:29:00'), 
('22/07/2012 23.58.23'), ('29/08/2011 23.58.02');

SELECT CONVERT(time, REPLACE(MESSAGE_DATE, '.', ':'), 103)
FROM @T;

Answer 5

Hi @sergio magrì ，

The conversion between date type and other types generally directly use cast or convert(CAST and CONVERT (Transact-SQL)),

-- CAST Syntax:    
CAST ( expression AS data_type )    
    
-- CONVERT Syntax:    
CONVERT ( data_type  , expression , style )--style can specify the date format for conversion  

or you can modify the table structure and change the character type to a date structure.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.