Correcting times

Question

I have a table that has a datetime field called [avg time to answer]. For certain records the time is displaying incorrectly as shown below:

1899-12-30 01:04:00.000
1899-12-30 01:05:00.000
1899-12-30 01:30:00.000

The times shown here are wrong. Times shown above are as small sample but they need to be formatted in minutes seconds rather than hours and minutes

Is there a way to correct this with a SQL script as the alternative involves extracting the data again and that is going to be extremely difficult and time consuming to do.

Answer 1

You can use DATEPART:

Returns an integer that represents the specified datepart of the specified date.

Combined with DATEADD:

Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.

Example:

DECLARE @val AS DATETIME = '1899-12-30 01:30:00.000';

SELECT  @val; -- 1899-12-30 01:30:00.000

SET @val = DATEADD(SECOND, DATEPART(MINUTE, @val),
                   DATEADD(MINUTE, ( DATEPART(HOUR, @val) ),
                           CAST(CAST(@val AS DATE) AS DATETIME)));

SELECT  @val; -- 1899-12-30 00:01:30.000

This casts the value to a date, to strip off the time portion and back to a datetime to set the time to 00:00:00. Then adds minutes based on the hour value and seconds based on the minute value.

You can use this with an UPDATE statement, but ensure you use a WHERE clause to avoid updating everything, unless that's what you intend.

Example Update Query:

CREATE TABLE #temp
    (
      AvgTimeToAnswer DATETIME
    );

INSERT  INTO #temp
        ( AvgTimeToAnswer )
VALUES  ( '1899-12-30 01:30:00.000' );

SELECT  *
FROM    #temp; -- 1899-12-30 01:30:00.000

UPDATE  #temp
SET     AvgTimeToAnswer = DATEADD(SECOND, DATEPART(MINUTE, AvgTimeToAnswer),
                              DATEADD(MINUTE,
                                      ( DATEPART(HOUR, AvgTimeToAnswer) ),
                                      CAST(CAST(AvgTimeToAnswer AS DATE) AS DATETIME)));
-- WHERE some condition

SELECT  *
FROM    #temp; -- 1899-12-30 00:01:30.000

DROP TABLE #temp;