How to convert 3/4 Character Integer to 24 Hour Ti

问题:

I have a doc Column of Date Time format, but it always leaves the time blank, and I have a time field which is an integer which represents hours and minutes (not including seconds). I would like to convert the time and add it to the date so that I can select Max(datetime) and return a single value.

Here is my SQL Fiddle.

The Data is stored as follows:

DocDate                 DocTime
2013-04-29 00:00:00.000 1416
2013-04-29 00:00:00.000 1027
2013-04-29 00:00:00.000 823

I'd like to convert it to:

DateTime
2013-04-29 14:16:00.000 
2013-04-29 10:27:00.000 
2013-04-29 08:23:00.000 

回答1:

Some simple date arithmetics will do it;

SELECT DATEADD(mi, FLOOR(DocTime/100)*60 + DocTime%100, DocDate) FROM OPOR;

An SQLfiddle to test with.

回答2:

This should do:

;WITH CTE AS
(
    SELECT  *,
            DATEADD(HOUR,DOCTIME/100,DOCDATE) DocDateHour,
            CAST(RIGHT(DOCTIME,2) AS INT) DocTimeOnly
    FROM OPOR
)
SELECT DATEADD(MINUTE,DocTimeOnly,DocDateHour)
FROM CTE

And here is the modified sqlfiddle for you to try.

回答3:

see Sql fiddle

select 
dateadd(hh, convert(int,left(doctime,len(doctime)-2)),
dateadd(mi, convert(int,right(doctime,2)) ,docdate))
from opor

Using dateadd to add the hours ( left one or two chars of doctime) and the minutes (right 2 chars of doctime) to the date.

回答4:

Just treat them both as datetimes and add together:

select  DocDate,
        DocTime,
        DocDate + stuff(right('0000'+cast(DocTime as varchar(4)), 4), 3, 0, ':')
from    OPOR

See example: http://sqlfiddle.com/#!3/8e22e/54