How do I create a datetime from a custom format st

Question

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.

How do I perform this conversion? I created the view but can't find a way to convert the string to a datetime.

Thanks!

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

SELECT  
        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

Answer 1

DECLARE @d VARCHAR(12);

SET @d = '201101011235';

SELECT CONVERT(SMALLDATETIME, STUFF(STUFF(@d,9,0,' '),12,0,':'));

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 NULL -- NULL
        UNION SELECT '20110101' -- yyyymmdd only
),
y(d, dt) AS 
(
    SELECT d,
        dt = STUFF(STUFF(LEFT(d+'000000',12),9,0,' '),12,0,':')
    FROM x
)
SELECT CONVERT(SMALLDATETIME, dt), ''
    FROM y
    WHERE ISDATE(dt) = 1 OR d IS NULL
UNION
SELECT NULL, d
    FROM y
    WHERE ISDATE(dt) = 0 AND d IS NOT NULL;