Changing the format of an AS column from text to datetime

Question

I am using this code to pull out a date and time from a text field.

    SELECT
    LEFT(SUBSTRING(eventcomments,
    CHARINDEX('Off-Site:', eventcomments) + 9, 17),
    CHARINDEX('Off-Site:', eventcomments)) AS Departed

which creates a Departed column dd/mm/yyyy hh:mm

I wanted to convert this from text to a datetime format so I added the following code.

    SELECT
    CONVERT(datetime,LEFT(SUBSTRING(eventcomments,
    CHARINDEX('Off-Site:', eventcomments) + 9, 17),
    CHARINDEX('Off-Site:', eventcomments))) AS Departed

but I got the following error message,

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

My question is,

How can I change the format of the Departed column from text to a datetime format.

Answer 1

Just use the CAST function:

SELECT CAST(LEFT(SUBSTRING(eventcomments,
            CHARINDEX('Off-Site:', eventcomments) + 9, 17),
            CHARINDEX('Off-Site:', eventcomments)) 
          AS DATETIME) AS Departed

Like SELECT CAST('05/10/1983 19:00' as datetime)