How to split a datetime column into a date column and a concatenated time, for same date, column in SQL Server 2012

user_9415769 0
Viewed 71

I have a datetime column with several hours for the same date. I want to set the date, without repeating, in one column and the concatenated hours in another column.

The table structure is:

USERID    CHECKTIME
-----------------------------
89        02/02/2021 05:06:07
89        02/02/2021 09:07:08
89        02/02/2021 12:15:20
89        03/02/2021 10:11:12

The output I'm looking for is:

DATE          HOURS
------------------------------------------
02/02/2021    05:06:07, 09:07:08, 12:15:20
03/02/2021    10:11:12

I tried the following query, but, the resulting hours are the same for all the rows. I know it's because I'm specifying the values as literals, but, I couldn't find how to code the query so, the hours get concatenated for each different date:

SELECT DISTINCT 
    FORMAT(CHECKTIME,'dd/MM/yyyy') AS DATE,
    STUFF((SELECT ', '  + FORMAT(CHECKTIME, 'HH:mm:ss') 
           FROM dbo.CHECKINOUT_PD
           WHERE FORMAT(CHECKTIME, 'dd/MM/yyyy') = '02/02/2021' 
             AND USERID = 89
           FOR XML PATH ('')), 1, 2, '') AS HOURS
FROM 
    dbo.CHECKINOUT_PD
WHERE 
    FORMAT(CHECKTIME, 'MM/yyyy') = '02/2021' 
    AND USERID = 89

I'll appreciate any help provided.

Thank you in advance!

tsql sql-server sql
1 Answers

You just need to correlate your inner query with your outer query.

Note that format is very expensive and should be avoided where possible - especially when using in the where clause - its much better to compare in a native datatype rather than a string.

declare @Test table (UserId int, CheckTime datetime2(0))

insert into @Test (UserId, CheckTime)
values
(89, '02/02/2021 05:06:07'),
(89, '02/02/2021 09:07:08'),
(89, '02/02/2021 12:15:20'),
(89, '03/02/2021 10:11:12');

SELECT CAST(CHECKTIME AS date) AS [DATE]
    , STUFF((
        SELECT ', '  + FORMAT(T2.CHECKTIME, 'HH:mm:ss') 
        FROM @Test T2
        WHERE CAST(T2.CHECKTIME AS date) = CAST(T1.CHECKTIME AS date)
        AND T1.USERID = T2.USERID
        FOR XML PATH ('')
    ), 1, 2, '') AS [HOURS]
FROM @Test T1
WHERE T1.USERID = 89
GROUP BY T1.USERID, CAST(T1.CHECKTIME AS date);

You can use string_agg (if using SQL Server 2017+)

SELECT CAST(CHECKTIME AS date) AS [DATE]
    , STRING_AGG(FORMAT(T1.CHECKTIME, 'HH:mm:ss'),', ') WITHIN GROUP (order by T1.CHECKTIME asc) [HOURS]
FROM @Test T1
WHERE T1.USERID = 89
GROUP BY T1.USERID, CAST(T1.CHECKTIME AS date);

Note: If you add the DDL+DML for your sample data to future questions it makes it much easier to assist.

user_11274280
Related