I have a time shift column in my
tblCalendar table called
TimeShift and need to fill two other columns in the same table, both columns have
Null values and their names are
This serves as an example:
|8:00AM – 4:00PM||Null||Null|
|10:00AM – 6:00PM||Null||Null|
|11:00AM – 7:00PM||Null||Null|
I need to fill the
EndTime columns by doing a split on the first column.
I figured out how to use SQL functions to split the first column on ‘-‘ char.
But I didn’t find a way to fill the Null columns with these extracted split data.
The query I found is:
SELECT LEFT(TimeShift, CHARINDEX('-', TimeShift) - 2) AS StartTime, RIGHT(TimeShift, LEN(TimeShift) - CHARINDEX('-', TimeShift) -1) AS EndTime FROM tblCalendar
which will return the required data, but my question is: how to fill these values into the
EndTime columns in my original table?
Well, obviously, you’ll need an
UPDATE statement to update an existing table – and you can basically use the two expressions from your
Try something like this:
UPDATE dbo.tblCalendar SET StartTime = LEFT(TimeShift, CHARINDEX('-', TimeShift) - 2), EndTime = RIGHT(TimeShift, LEN(TimeShift) - CHARINDEX('-', TimeShift) -1) WHERE StartTime IS NULL AND EndTime IS NULL;
Depending on the datatypes of your columns, this might not work right away – maybe you also need to do some conversions from e.g.