How to preserve seconds when Concatenating two Date_Time columns

Question

I am getting the last update from two different columns. Either P1DT will be null or P2Dt will be null, and never both. I have successfully merged the two columns into one, but will need to preserve the seconds at the end of the number... what is the best way to go about this?

WITH tblDT AS
(
    SELECT DISTINCT 
        StudyNum,
        SerialNum,
        PuffCount,
        CONCAT((SELECT MAX(P1DT)
                FROM P3 P3B
                WHERE P3B.SerialNum = P3A.SerialNum
                  AND P3B.StudyNum = P3A.StudyNum),
               (SELECT MAX(P2DT)
                FROM P3 P3B
                WHERE P3B.SerialNum = P3A.SerialNum
                  AND P3B.StudyNum = P3A.StudyNum)) AS MaxDate    
    FROM 
        P3 P3A 
)
SELECT *
FROM tblDT

I tried concatenating the two columns into one. It works successfully, but does not preserve the seconds.

Answer 1

You want COALESCE() rather than CONCAT():

SELECT a.StudyNum, a.SerialNum, a.PuffCount
    , COALESCE(MAX(b.P1DT), MAX(b.P2DT)) MaxDate
FROM P3 a
INNER JOIN P3 b on b.SerialNum = a.SerialNum AND b.StudyNum = a.StudyNum
GROUP BY a.StudyNum, a.SerialNum, a.Puffcount

If you don't really need PuffCount, know it's always the same for a given StudyNum and SerialNum, or can otherwise infer it from an aggregate function, we can also remove the JOIN.

---

Hmm... thinking more, I think we can also do this with window functions:

SELECT DISTINCT StudyNum, SerialNum, PuffCount
    ,COALESCE(
       First_Value(P1DT) over (PARTITION BY StudyNum, SerialNum ORDER BY P1DT DESC),
       First_Value(P2DT) over (PARTITION BY StudyNum, SerialNum ORDER BY P2DT DESC)
    ) MaxDate
FROM P3

See them work here:

https://dbfiddle.uk/gTyoBoRS

---

If the original query runs at all using concat(), with no casting, then you don't really have DateTime values at all. What you have are string/varchar values pretending to be DateTime values, and that's really bad.