3

Get the date difference in days, hours, mins and seconds (AM / PM date)

 3 years ago
source link: https://www.codesd.com/item/get-the-date-difference-in-days-hours-mins-and-seconds-am-pm-date.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

Get the date difference in days, hours, mins and seconds (AM / PM date)

advertisements

Trying to get date difference in day - hours - mins - sec from a string entry of date. Also the time entry is having AM, PM. I am trying to use substring and converting it date time, but AM - PM is making it difficult.

I have: 06-OCT-15 09.30.20.000000 AM and 06-OCT-15 09.45.20.000000 PM

I am trying to get: 0 day(s) 12 hour(s) 15 min(s) 0 sec(s).

See below SQL Server query. I know this will work as I have some experience in this.

WITH CTE AS (
    SELECT CAST(
      DATEDIFF(
              DAY,
              '2015-10-06 08:00:00.00 AM',
              '2015-10-06 09:00:00.00 PM'
          ) AS
          VARCHAR(1000)
      ) + ' day(s)' AS      Days,
      CAST(
          DATEDIFF(
              hh,
              '2015-10-06 08:00:00.00 AM',
              '2015-10-06 09:00:00.00 PM'
          ) AS VARCHAR(1000)
      ) + ' hour(s)' AS     Hours,
      CAST(
          DATEDIFF(
              mi,
              '2015-10-06 08:00:00.00 AM',
              '2015-10-06 09:00:00.00 PM'
          ) AS VARCHAR(1000)
      ) + ' min(s)' AS      Minutes,
      CAST(
          DATEDIFF(
              ss,
              '2015-10-06 08:00:00.00 AM',
              '2015-10-06 09:00:00.00 PM'
          ) AS VARCHAR(1000)
      ) + ' sec(s)' AS Seconds
)
SELECT Days + ' ' + Hours + ' ' + Minutes + ' ' + Seconds AS FullDifference
FROM   CTE

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK