Convert varchar(7) columns to datetime

Question

I have this data that is in a varchar(7) column that I want to convert into a DateTime column.

I have a select where I am split this into month, year and setting the day to 1. I am having a hard time inserting this data into the LogDate [datetime]. I tried to CONCAT the select but I am getting Incorrect syntax . Is there any other way to do this?

Table setup

CREATE TABLE LogData
(
    LogMonth [varchar](7) NOT NULL,
    LogDate [datetime] NULL
);

INSERT INTO LogData ([LogMonth])
VALUES ('01/2023');

INSERT INTO LogData ([LogMonth])
VALUES ('02/2023');

INSERT INTO LogData ([LogMonth])
VALUES ('03/2023');

Insert

SELECT 
    REVERSE(PARSENAME(REPLACE(REVERSE(LogMonth), '/', '.'), 1)) AS [Month],
    REVERSE(PARSENAME(REPLACE(REVERSE(LogMonth), '/', '.'), 2)) AS [Year],
    [Day] = 1
FROM 
    LogData

This is what I have tried:

INSERT INTO LogData (LogDate)
    SELECT 
        CONCAT(REVERSE(PARSENAME(REPLACE(REVERSE(LogMonth), '/', '.'), 1)) AS [Month],
               REVERSE(PARSENAME(REPLACE(REVERSE(LogMonth), '/', '.'), 2)) AS [Year],
               [Day] = 1) 

Sample

http://sqlfiddle.com/#!18/16bbc/1

Answer 1

You will actually want to construct a date from the information that you have. One way to do this is DATEFROMPARTS. It takes Year, Month and Day and converts it into a date.

SELECT 
  DATEFROMPARTS(RIGHT(LogMonth, 4), LEFT(LogMonth, 2), 1)
FROM LogData

In your sqlfiddle example, you will have to use UPDATE instead of INSERT, as you already made some data:

UPDATE LogData 
   SET LogDate = DATEFROMPARTS(RIGHT(LogMonth, 4), LEFT(LogMonth, 2), 1);

Have a look: sqlfiddle