How to convert datetime string without delimiters

Question

I am looking to convert a string like this: 20160520191959550 - that is actually date-time for 2016-05-20 19:19:59.

I tried using CAST as datetime in a SQL statement, but got this error:

Conversion failed when converting date and/or time from character string.

Here is the SQL:

Select Cast(vbon.DATE_INS As datetime) As DINS 
From vbon 

I routinely use CAST on date strings without delimiters and am looking for a similar solution. Is there a simple way to get the job done without reverting to LEFT, RIGHT and SUBSTRING?

Answer 1

You can try this:

SELECT CONVERT(VARCHAR(8), vbon.DATE_INS, 112) + REPLACE(CONVERT(varchar, vbon.DATE_INS, 108), ':','')

Ref: Convert DateTime to yyyyMMddHHmm in T-SQL

Answer 2

In SQL Server 2012 and later, you can use DATETIMEFROMPARTS:

DECLARE @DateTimeString varchar(19) = '20160520191959550';
SELECT DATETIMEFROMPARTS(
      SUBSTRING(@DateTimeString,1,4)
    , SUBSTRING(@DateTimeString,5,2)
    , SUBSTRING(@DateTimeString,7,2)
    , SUBSTRING(@DateTimeString,9,2)
    , SUBSTRING(@DateTimeString,11,2)
    , SUBSTRING(@DateTimeString,13,2)
    , SUBSTRING(@DateTimeString,15,3)
    );

In earlier versions, one method is to build a ISO 8601 formatted string and use CAST or CONVERT:

SELECT CAST(
      SUBSTRING(@DateTimeString,1,4)
     + '-' + SUBSTRING(@DateTimeString,5,2)
     + '-' + SUBSTRING(@DateTimeString,7,2)
     + 'T' + SUBSTRING(@DateTimeString,9,2)
     + ':' + SUBSTRING(@DateTimeString,11,2)
     + ':' + SUBSTRING(@DateTimeString,13,2)
     + '.' + SUBSTRING(@DateTimeString,15,3)
    AS datetime2(3));

Answer 3

I suggest you to create function:

CREATE FUNCTION dbo.datestringtodatetime
(
    -- Add the parameters for the function here
    @d NVARCHAR(max)
)
RETURNS datetime
AS
BEGIN
    -- Declare the return variable here
    DECLARE @table TABLE (
        id int,
        [pos] int,
        [sym] char(1)
    )

    DECLARE @output datetime
    --In this table we determine were to put delimeters
    INSERT INTO @table VALUES
    (1, 5,'-'),
    (2, 8,'-'),
    (3, 11,'T'),
    (4, 14,':'),
    (5, 17,':'),
    (6, 20,'.')

    ;WITH cte AS (
        SELECT STUFF(@d,t.[pos],0,t.[sym]) as d_, 1 as l
        FROM @table t
        WHERE t.id = 1
        UNION ALL
        SELECT STUFF(c1.d_,t.[pos],0,t.[sym]), l+1
        FROM cte c1
        INNER JOIN @table t
            ON t.id = l+1
    )

    SELECT @output = TRY_CAST(d_ as datetime)
    FROM cte
    WHERE l = 6
    -- Return the result of the function
    RETURN @output

END
GO

You can call it like:

SELECT [dbo].[datestringtodatetime] ('20160520191959550')

Output:

2016-05-20 19:19:59.550

If you pass it wrong value you will get NULL

Answer 4

SELECT CAST(
    STUFF(
        STUFF(
            STUFF(
                STUFF('20160520191959550', 9, 0, ' ')
            , 12, 0, ':')
        , 15, 0, ':')
    , 18, 0, '.') AS DATETIME)

Answer 5

I think convert would work for you. I can't test right now but I have done what you are looking for with dates. Here is a good article: http://www.sqlusa.com/bestpractices/datetimeconversion/

Answer 6

Addtional variant to already posted:

SELECT  CONVERT(DATETIME, FORMAT(CONVERT(BIGINT, '20160520191959550'),
                                 '####-##-## ##:##:##"."###'))