How do I create a datetime from a custom format string?

I have datetime values stored in a field as strings. They are stored as strings because that’s how they come across the wire and the raw values are used in other places.

For reporting, I want to convert the custom format string (yyyymmddhhmm) to a datetime field in a view. My reports will use the view and work with real datetime values. This will make queries involving date ranges much easier.

  • Creating bcp format file from local database table - connection error
  • How can I group by arbitary time period with SQL
  • Database trigger that communicates with an external program
  • How to make that sql view to show data differences
  • How do I update a XML string in an ntext column in SQL Server?
  • Connecting to MSSQL with Codeigniter PHP
  • How do I perform this conversion? I created the view but can’t find a way to convert the string to a datetime.


    Update 1 –

    Here’s the SQL I have so far. When I try to execute, I get a conversion error “Conversion failed when converting datetime from character string.”

    How do I handle nulls and datetime strings that are missing the time portion (just yyyymmdd)?

            dbo.PV1_B.PV1_F44_C1 AS ArrivalDT,
    cast(substring(dbo.PV1_B.PV1_F44_C1, 1, 8)+' '+substring(dbo.PV1_B.PV1_F44_C1, 9, 2)+':'+substring(dbo.PV1_B.PV1_F44_C1, 11, 2) as datetime) AS ArrDT,
            dbo.MSH_A.MSH_F9_C2 AS MessageType,
            dbo.PID_A.PID_F3_C1 AS PRC,
            dbo.PID_A.PID_F5_C1 AS LastName, 
            dbo.PID_A.PID_F5_C2 AS FirstName,
            dbo.PID_A.PID_F5_C3 AS MiddleInitial,
            dbo.PV1_A.PV1_F2_C1 AS Score, 
            dbo.MSH_A.MessageID AS MessageId
    FROM    dbo.MSH_A
            INNER JOIN dbo.PID_A ON dbo.MSH_A.MessageID = dbo.PID_A.MessageID
            INNER JOIN dbo.PV1_A ON dbo.MSH_A.MessageID = dbo.PV1_A.MessageID
            INNER JOIN dbo.PV1_B ON dbo.MSH_A.MessageID = dbo.PV1_B.MessageID

    7 Solutions collect form web for “How do I create a datetime from a custom format string?”

    declare @S varchar(12)
    set @S = '201107062114'
    select cast(substring(@S, 1, 8)+' '+substring(@S, 9, 2)+':'+substring(@S, 11, 2) as datetime)


    2011-07-06 21:14:00.000'

    This first changes your date string to 20110706 21:14. Date format yyyymmdd as a string is safe to convert to datetime in SQL Server regardless of SET DATEFORMAT setting.


    declare @T table(S varchar(12))
    insert into @T values('201107062114')
    insert into @T values('20110706')
    insert into @T values(null)
      case len(S)
        when 12 then cast(substring(S, 1, 8)+' '+substring(S, 9, 2)+':'+substring(S, 11, 2) as datetime)
        when 8 then cast(S as datetime)
    from @T


    2011-07-06 21:14:00.000
    2011-07-06 00:00:00.000

    According to here, there’s no out-of-the-box CONVERT to get from your yyyymmddhhmm format to datetime.

    Your strategy will be parsing the string to one of the formats provided on the documentation, then convert it.

    You can use CAST or CONVERT.

    Example from the site:

    G. Using CAST and CONVERT with
    datetime data

    The following example displays the
    current date and time, uses CAST to
    change the current date and time to a
    character data type, and then uses
    CONVERT display the date and time in
    the ISO 8901 format.

       GETDATE() AS UnconvertedDateTime,
       CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
       CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601;

    Here is the result set.

    UnconvertedDateTime     UsingCast                      UsingConvertTo_ISO8601
    ----------------------- ------------------------------ ------------------------------
    2006-04-18 09:58:04.570 Apr 18 2006 9:58AM             2006-04-18T09:58:04.570
    (1 row(s) affected)

    Generally, you can use this code:

    SELECT convert(datetime,'20110706',112)

    If you need to force SQL Server to use a custom format string, use the following code:

    SELECT convert(datetime,'20110706')

    A one liner:

    declare @datestring varchar(255)
    set @datestring = '201102281723'
    select convert(datetime, stuff(stuff(@datestring,9,0,' '),12,0,':') , 112 )


    2011-02-28 17:23:00.000
    SET @d = '201101011235';

    Note that by storing date/time data using an inappropriate data type, you cannot prevent bad data from ending up in here. So it might be safer to do this:

    WITH x(d) AS
        SELECT d = '201101011235'
            UNION SELECT '201101011267' -- not valid
            UNION SELECT '20110101' -- yyyymmdd only
    y(d, dt) AS 
        SELECT d,
            dt = STUFF(STUFF(LEFT(d+'000000',12),9,0,' '),12,0,':')
        FROM x
        FROM y
        WHERE ISDATE(dt) = 1 OR d IS NULL
        FROM y
        WHERE ISDATE(dt) = 0 AND d IS NOT NULL;
    DECLARE @test varchar(100) = '201104050800'
    DECLARE @dt smalldatetime
    SELECT @dt = SUBSTRING(@test, 5, 2) 
                 + '/' + SUBSTRING(@test, 7, 2) + '/' 
                 + SUBSTRING(@test, 1, 4) + ' ' + SUBSTRING(@test, 9, 2) 
                 + ':' + SUBSTRING(@test, 11, 2)
    SELECT @dt


    2011-04-05 08:00:00

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.