I have a column that is in the format of
2/23/23 9:00 am - 2/23/23 9:59 am
I am using a select statement to get the start date and start time from this column as follows:
start date: LTRIM(RTRIM(CONVERT(DATE,RTRIM(LTRIM(LEFT([Date], CHARINDEX(' ',[Date]) + 0))))))
start time: RTRIM(LTRIM(FORMAT(CAST(REPLACE(REPLACE(RTRIM(LTRIM(RIGHT(RIGHT(LEFT([Date], CHARINDEX('-', [Date]) - 1), LEN(LEFT([Date], CHARINDEX('-', [Date]) - 1)) - PATINDEX( '%/[12][0-9] %',LEFT([Date], CHARINDEX('-', [Date]) - 1))),LEN(RIGHT(LEFT([Date], CHARINDEX('-', [Date]) - 1), LEN(LEFT([Date], CHARINDEX('-', [Date]) - 1)) - PATINDEX( '%/[12][0-9] %',LEFT([Date], CHARINDEX('-', [Date]) - 1)))) - 1))),'am','AM'),'pm','PM') AS datetime),'hh:mm tt')))
Desired output for time: 9:00 am
The start date works but for start time I am having issues now when the year is formatted as /2023 instead of /23 because I will have both of these year formats included in my table.
What I tried is adding another % to account for the 2 extra digits in the year as follows:
PATINDEX( '%/%[12][0-9] %'
but this gives me 'Conversion failed when converting date and/or time from character string.' error.
What should I change in my start time select statement to account for having both '2023' and '23' in the year part.
If you want to get time difference between two dates, you can use something like this:
;WITH InitialData(TimeRange) AS
(
SELECT '2/23/23 9:00 am - 2/23/23 9:59 am'
),
CTE( StartDate, EndDate ) AS
(
SELECT CAST(LEFT(TimeRange, CHARINDEX('-', TimeRange)-2) AS DATETIME2),
CAST(RIGHT(TimeRange,LEN(TimeRange)-CHARINDEX('-', TimeRange)-1) AS DATETIME2)
FROM InitialData
)
SELECT DATEDIFF(MI, StartDate, EndDate) AS TimeDiff
FROM CTE;
Working example here: DbFiddle
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments