SQL adds date conversion to existing query

Question

I want to convert the date using this function. I am using SQL Server 2014

CONVERT(varchar, sc.StartDate,103) + '" + " " + "' + '" + " - " + "' + CONVERT(varchar, sc.EndDate,103) SIPDate 

so that it is displayed in the format dd / mm / yyyy - dd / mm / yyyy. How to add it to the request? Thank you

Inquiry:

SELECT CONCAT(sc.StartDate, sc.ENDDate) SIPDate,
   COUNT (sj.LOComment) WeekReviewed,
   COUNT(sjd.WeekNo) TotalWeek,
   SUM(sjj.TotalDaysRecord) TotalDaysRecord,
   COUNT(CASE sjd.JournalStatusCode WHEN 'D' THEN 1 ELSE NULL END) PENDingComplete
FROM StudentJournalDate sjd
LEFT JOIN StudentJournal sj
    ON sjd.WeekNo = sj.WeekNo
LEFT OUTER JOIN
(
    SELECT sj.WeekNo, 
           CASE WHEN RTRIM(sj.Day1Journal) = '' OR sj.Day1Journal IS NULL THEN 0 ELSE 1 END +
           CASE WHEN RTRIM(sj.Day2Journal) = '' OR sj.Day2Journal IS NULL THEN 0 ELSE 1 END +
           CASE WHEN RTRIM(sj.Day3Journal) = '' OR sj.Day3Journal IS NULL THEN 0 ELSE 1 END +
           CASE WHEN RTRIM(sj.Day4Journal) = '' OR sj.Day4Journal IS NULL THEN 0 ELSE 1 END +
           CASE WHEN RTRIM(sj.Day5Journal) = '' OR sj.Day5Journal IS NULL THEN 0 ELSE 1 END +
           CASE WHEN RTRIM(sj.Day6Journal) = '' OR sj.Day6Journal IS NULL THEN 0 ELSE 1 END +
           CASE WHEN RTRIM(sj.Day7Journal) = '' OR sj.Day7Journal IS NULL THEN 0 ELSE 1 END AS TotalDaysRecord 
    FROM StudentJournal sj
) AS sjj
    ON sjj.WeekNo = sj.WeekNo
LEFT OUTER JOIN StudentSIP sc
    ON sc.AdminNo = sjd.AdminNo
GROUP BY CONCAT(sc.StartDate, sc.ENDDate)

Answer 1

You can simply SELECTand GROUP BYthis is a formatted date expression, i.e.:

SELECT CONVERT(varchar, sc.StartDate, 103) + " - " + CONVERT(varchar, sc.EndDate, 103) AS SIPDate,
       COUNT (sj.LOComment) WeekReviewed,
       COUNT(sjd.WeekNo) TotalWeek,
       SUM(sjj.TotalDaysRecord) TotalDaysRecord,
       COUNT(CASE sjd.JournalStatusCode WHEN 'D' THEN 1 ELSE NULL END) PENDingComplete
FROM StudentJournalDate sjd
LEFT JOIN StudentJournal sj
    ON sjd.WeekNo = sj.WeekNo
LEFT OUTER JOIN
(
    SELECT sj.WeekNo, 
           CASE WHEN RTRIM(sj.Day1Journal) = '' OR sj.Day1Journal IS NULL THEN 0 ELSE 1 END +
           CASE WHEN RTRIM(sj.Day2Journal) = '' OR sj.Day2Journal IS NULL THEN 0 ELSE 1 END +
           CASE WHEN RTRIM(sj.Day3Journal) = '' OR sj.Day3Journal IS NULL THEN 0 ELSE 1 END +
           CASE WHEN RTRIM(sj.Day4Journal) = '' OR sj.Day4Journal IS NULL THEN 0 ELSE 1 END +
           CASE WHEN RTRIM(sj.Day5Journal) = '' OR sj.Day5Journal IS NULL THEN 0 ELSE 1 END +
           CASE WHEN RTRIM(sj.Day6Journal) = '' OR sj.Day6Journal IS NULL THEN 0 ELSE 1 END +
           CASE WHEN RTRIM(sj.Day7Journal) = '' OR sj.Day7Journal IS NULL THEN 0 ELSE 1 END AS TotalDaysRecord 
    FROM StudentJournal sj
) AS sjj
    ON sjj.WeekNo = sj.WeekNo
LEFT OUTER JOIN StudentSIP sc
    ON sc.AdminNo = sjd.AdminNo
GROUP BY CONVERT(varchar, sc.StartDate, 103) + " - " + CONVERT(varchar, sc.EndDate, 103)

Answer 2

Try it. since your SQL SERVER is 2014, you can use the format to format the date

SELECT     CONCAT(FORMAT(sc.StartDate,'dd/MM/yyy'),+ ' - ' +FORMAT(sc.ENDDate,'dd/MM/yyy'))  SIPDate,
   COUNT (sj.LOComment) WeekReviewed,
   COUNT(sjd.WeekNo) TotalWeek,
   SUM(sjj.TotalDaysRecord) TotalDaysRecord,
   COUNT(CASE sjd.JournalStatusCode WHEN 'D' THEN 1 ELSE NULL END) PENDingComplete
FROM StudentJournalDate sjd
LEFT JOIN StudentJournal sj
    ON sjd.WeekNo = sj.WeekNo
LEFT OUTER JOIN
(
    SELECT sj.WeekNo, 
           CASE WHEN RTRIM(sj.Day1Journal) = '' OR sj.Day1Journal IS NULL THEN 0 ELSE 1 END +
           CASE WHEN RTRIM(sj.Day2Journal) = '' OR sj.Day2Journal IS NULL THEN 0 ELSE 1 END +
           CASE WHEN RTRIM(sj.Day3Journal) = '' OR sj.Day3Journal IS NULL THEN 0 ELSE 1 END +
           CASE WHEN RTRIM(sj.Day4Journal) = '' OR sj.Day4Journal IS NULL THEN 0 ELSE 1 END +
           CASE WHEN RTRIM(sj.Day5Journal) = '' OR sj.Day5Journal IS NULL THEN 0 ELSE 1 END +
           CASE WHEN RTRIM(sj.Day6Journal) = '' OR sj.Day6Journal IS NULL THEN 0 ELSE 1 END +
           CASE WHEN RTRIM(sj.Day7Journal) = '' OR sj.Day7Journal IS NULL THEN 0 ELSE 1 END AS TotalDaysRecord 
    FROM StudentJournal sj
) AS sjj
    ON sjj.WeekNo = sj.WeekNo
LEFT OUTER JOIN StudentSIP sc
    ON sc.AdminNo = sjd.AdminNo
GROUP BY CONCAT(FORMAT(sc.StartDate,'dd/MM/yyy'),+ ' - ' +FORMAT(sc.ENDDate,'dd/MM/yyy'))