Convert Varchar to Time

Convert varchar to time

The string is in the wrong format. It should be 09:47:11.895799. You need to replace the final : with a .

SELECT CAST(STUFF('09:47:11:895799', 9,1,'.') AS TIME)

How to convert DateTime to VarChar

With Microsoft Sql Server:

--
-- Create test case
--
DECLARE @myDateTime DATETIME
SET @myDateTime = '2008-05-03'

--
-- Convert string
--
SELECT LEFT(CONVERT(VARCHAR, @myDateTime, 120), 10)

Convert varchar to time - T-SQL

You can try this -

DECLARE @SmsStartTime AS VARCHAR(2) = 16 

SELECT CAST(@SmsStartTime + ':00:00' AS TIME)

Result

16:00:00.0000000

Conversion from varchar to Datetime

You can use STUFF to inject the needed characters in the right place to get the format yyyyMMdd hh:mm:ss which is also unambiguous in SQL Server:

SELECT V.YourColumn,
       CONVERT(datetime2(0),STUFF(STUFF(STUFF(LEFT(V.YourColumn,14),13,0,':'),11,0,':'),9,0,' '))
FROM (VALUES('20220101000009CS'))V(YourColumn);

converting varchar to datetime in sql

The error caused by your special 20181015 151706 varchar DateTime value,

you can try to use substring function make the DateTime format string value then do convert.

SELECT CONVERT(DATETIME,CONCAT(substring(col, 1, 4),'-',substring(col, 5, 2),'-',substring(col, 7, 2),' ',substring(col, 9, 2),':',substring(col, 11, 2),':',substring(col, 13, 2)))
FROM (
   select REPLACE('20181015 151706',' ','') col
) t1

sqlfiddle

---

Related Topics