question

sergiomagr-1314 avatar image
0 Votes"
sergiomagr-1314 asked CEO-8149 commented

Sql Server 2008 Conversion from string to time

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

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

sergiomagr-1314 avatar image
0 Votes"
sergiomagr-1314 answered

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 :)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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)


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

sergiomagr-1314 avatar image
0 Votes"
sergiomagr-1314 answered CEO-8149 commented

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

· 10
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Confirm that some of values contain '.' instead of ':'.

0 Votes 0 ·

you are right, i did not see those '.'

0 Votes 0 ·

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?130760-sqlinsertcool.jpg


0 Votes 0 ·
sqlinsertcool.jpg (53.0 KiB)

i tried replacing the '.' with ':'.. same error

0 Votes 0 ·

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



0 Votes 0 ·
Show more comments

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

0 Votes 0 ·
Show more comments
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered sergiomagr-1314 commented
 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;
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

If that answered your question correctly please accept the answer so that it can help others who have similar questions.

0 Votes 0 ·

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

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered sergiomagr-1314 commented

Hi @sergiomagr-1314

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.





· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·