I am trying to get the start date and start times into 2 separate columns in a select query from a column that has a range of date time.

So far I am able to do the start date using this:

LTRIM(RTRIM(CONVERT(DATE,RTRIM(LTRIM(LEFT([Date], CHARINDEX(' ',[Date])   0))))))

I am encountering errors with my start time, the code I am at right now is this:

RTRIM(LTRIM(FORMAT(CAST(REPLACE(REPLACE(RTRIM(LTRIM(RIGHT(RIGHT(LEFT([Date], CHARINDEX('-', [Date]) - 1), LEN(LEFT([Date], CHARINDEX('-', [Date]) - 1)) - CHARINDEX('/21 ',LEFT([Date], CHARINDEX('-', [Date]) - 1))),LEN(RIGHT(LEFT([Date], CHARINDEX('-', [Date]) - 1), LEN(LEFT([Date], CHARINDEX('-', [Date]) - 1)) - CHARINDEX('/21 ',LEFT([Date], CHARINDEX('-', [Date]) - 1)))) - 1))),'am','AM'),'pm','PM') AS datetime),'hh:mm tt')))

I wanted the format to be hh:mm am/pm. I think what is causing the error is the counts/index of the characters but I am not sure how to resolve this.

This is my desired result:

CodePudding user response：

Using the base strings functions we can try:

SELECT
    Date,
    LEFT(Date, CHARINDEX(' ', Date) - 1) AS Date,
    SUBSTRING(Date,
              CHARINDEX(' ', Date)   1,
              CHARINDEX(' ', Date, CHARINDEX('-', Date)   1) -
                  CHARINDEX(' ', Date) - 3) AS Time
FROM yourTable;

Demo