I am having an issue converting below to a DateTime, I am only able to get the date portion of the string. My goal is to get both the date & time.

declare @col varchar(14) = '20220602235900';
select CONVERT(datetime, CAST(@col AS varchar(8)), 121) dtimecre

CodePudding user response：

Steps:

1. Read the documentation for CONVERT and pick the closest format to use
2. Do some string manipulation to get the desired format
3. Convert.

DECLARE @col varchar(14) = '20220602235900';

SELECT
    CONVERT(date, SUBSTRING(@col,1,8), 121) [Date Component]
    , CONVERT(time, SUBSTRING(@col,9,2)   ':'   SUBSTRING(@col,11,2)   ':'   SUBSTRING(@col,13,2), 8) [Time Component]
    , CONVERT(datetime, SUBSTRING(@col,1,4)   '-'   SUBSTRING(@col,5,2)   '-'   SUBSTRING(@col,7,2)   ' '   SUBSTRING(@col,9,2)   ':'   SUBSTRING(@col,11,2)   ':'   SUBSTRING(@col,13,2), 120) [DateTime Representation];

Returns:

CodePudding user response：

And another variant with a smaller amount of SUBSTRING:

DECLARE @col varchar(14) = '20220602235900';

SELECT DateComponent = CAST(DatePartString AS DATE),
       TimeComponent = CAST(TimePartString AS TIME),
       DateAndTime   = CAST(DatePartString   ' '   TimePartString AS DATETIME)
FROM (VALUES(@col)) AS O (DateColumn)
CROSS APPLY (
    SELECT DatePartString = LEFT(DateColumn, 8),
           TimePartString = FORMAT(CAST(SUBSTRING(DateColumn, 9, 6) AS INT), '##:##:##')
) AS String;