Sql Server 2008 Conversion from string to time

Question

Sql Server 2008 Conversion from string to time

sergio magrì 96

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 accepted by question author

4 additional answers

Your answer

Answer 1

sergio magrì 96

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

EchoLiu-MSFT 14,626

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.

sergio magrì 96 Reputation points

2020-10-01T08:54:00.337+00:00

thank you for the answer, i know that but i can't change table structure, it has existed for many years

Answer 3

Guoxiong 8,216

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;

sergio magrì 96 Reputation points

2020-10-01T07:17:08.33+00:00

thank you for the answer.
It works correclty, this is the output:

T
23:52:25.0000000
23:29:00.0000000
23:58:23.0000000
23:58:02.0000000
Guoxiong 8,216 Reputation points

2020-10-01T16:20:26.67+00:00

If that answered your question correctly please accept the answer so that it can help others who have similar questions.
sergio magrì 96 Reputation points

2020-10-01T16:22:34.793+00:00

no, if i applied your suggestion to the query on my table it fails.

Answer 4

sergio magrì 96

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

Viorel 126.3K Reputation points

2020-09-30T13:24:22.423+00:00

Confirm that some of values contain '.' instead of ':'.
sergio magrì 96 Reputation points

2020-09-30T14:06:15.937+00:00

you are right, i did not see those '.'
sergio magrì 96 Reputation points

2020-09-30T14:22:19.947+00:00

i tried replacing the '.' with ':'.. same error
Viorel 126.3K Reputation points

2020-09-30T18:00:21.59+00:00

If possible, show some details about this column, including collation. For example, execute exec sp_help 'MyTable' and show the column information.

Also find any problematic row and give the next two values using a query like this:

select MESSAGE_DATE, cast(MESSAGE_DATE as varbinary(50))
from MyTable
sergio magrì 96 Reputation points

2020-10-01T07:13:47.807+00:00

this is the output (sorry for the indentetation):

Name Owner Type Created_datetime
IFOX_ORDER_MESSAGE_OUTBOUND dbo user table 2017-06-20 10:20:27.427

Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
ROWNUMBER int no 4 10 0 no (n/a) (n/a) NULL
MESSAGE_TYPE varchar no 20 yes no yes Latin1_General_CI_AI
MESSAGE_DATE varchar no 20 yes no yes Latin1_General_CI_AI
FROM_UNIT_CODE varchar no 12 yes no yes Latin1_General_CI_AI
FROM_UNIT_MESSAGE_ID varchar no 10 yes no yes Latin1_General_CI_AI

RowGuidCol
No rowguidcol column defined.

Data_located_on_filegroup
PRIMARY

And this it the results of your query:

MESSAGE_DATE varbin
30/04/2019 23:52:25 0x33302F30342F323031392032333A35323A3235
13/11/2017 23:29:00 0x31332F31312F323031372032333A32393A3030
22/07/2012 23.58.23 0x32322F30372F323031322032332E35382E3233
29/08/2011 23.58.02 0x32392F30382F323031312032332E35382E3032
sergio magrì 96 Reputation points

2020-10-01T08:09:09.66+00:00

I tried to update MESSAGE_DATE within first row and querying top 1 give always the same error.. I think I'm making a stupid mistake, but I don't really understand which one
Uri Dimant 211 Reputation points

2020-10-01T08:13:48.343+00:00

DECLARE @d AS NVARCHAR(50)='30/04/2019 23:52:25'
SELECT CONVERT(TIME,CONVERT(VARCHAR(24), CONVERT(DATETIME, @d, 103), 114)) Date
sergio magrì 96 Reputation points

2020-10-01T08:51:15.06+00:00

Thank u for the answer, your example works.. the problem is when i read message_date from that column in that table
Uri Dimant 211 Reputation points

2020-10-01T08:53:57.427+00:00

I am not sure what you meant , but try
SELECT CONVERT(TIME,CONVERT(VARCHAR(24), CONVERT(DATETIME, message_date, 103), 114)) Date
FROM tblname
CEO 136 Reputation points

2021-09-09T19:05:27.153+00:00

Please I am having similar issue. I actually designed an application GUI, the GUI has a form with different input variables (textfield ). There's also a textfield for date and the date gets inputted automatically.

After all the data have been entered in their textfields of the form, I submit the record and they get saved in SQL database.

The database column for dateRecorded has data type of varchar(500).

So when application form is submitted, every column record gets saved in their respective columns in the database table.

I have no issue inserting and retrieving record. It's just about 3hours ago I discovered I'm unable to delete any record from the table. It comes up with an SQL error message that conversion failed when converting date and/or time.

I am so confused. I was wondering why a deletion process would have anything to do with conversion. I am about deleting the record, not making any transaction with it. Why is it giving me this error please?

I am attaching a photo showing a record with the stored dateRecord.

Meanwhile I've been deleting records from this same table before. I'm surprised it no longer works as it used it.

How do I resolve this please?

Answer 5

Viorel 126.3K

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)

Share via

Sql Server 2008 Conversion from string to time

4 additional answers

Your answer