SQL Server Function to Get How Long in Word

This below SQL User Defined Function script is to returns How Long the date already passed, in wording. It's usually displayed in articles to inform the reader how long the article already posted.

Return SQL Server 2016 JSON format In 1 row format

The newest version of Microsoft SQL Server, which it version 2016, comes with JSON native support. So you don't need to convert anymore at script side with 3rd party module like Newtonsoft library.

SQL syntax to show all days in a week

This is SQL SERVER trick is to show the entire daily data from database even there is no transaction data in a particular day

Let say you have a table with the below structure

Table: dailysharelike
Columns:
datetimetrans DATETIME
share_number INT
like_number INT

The data rows are
2016-09-05 00:00:00 | 2   | 1
2016-09-05 00:00:00 | 2   | 3
2016-08-25 00:00:00 | 10  | 1
2016-08-30 00:00:00 | 100 | 90
2016-08-25 00:00:00 | 150 | 80

If you do the query with simple query:

SELECT DATEPART(dw,datetimetrans) AS weekdaynum, DATENAME(dw,datetimetrans) AS weekdayname,
 ISNULL(SUM(like_number),0) AS like_number,
 ISNULL(SUM(share_number),0) AS share_number
 FROM dailysharelike
 GROUP BY DATEPART(dw,datetimetrans), DATENAME(dw,datetimetrans)
 GROUP BY DATEPART(dw,datetimetrans), DATENAME(dw,datetimetrans)

then you will get this (without Sunday, Wednesday, Friday and Saturday)

2 | Monday   | 4   | 4
3 | Tuesday  | 100 | 90
5 | Thursday | 160 | 81

To show all the days in a week, you can use this syntax

SELECT DISTINCT weekdaynum, weekdayname, SUM(like_number) AS like_number,
SUM(share_number) AS share_number FROM
(SELECT 1 AS weekdaynum, 'Sunday' AS weekdayname, 0 AS like_number, 0 AS share_number UNION
 SELECT 2, 'Monday' AS weekdayname, 0 AS like_number, 0 AS share_number UNION
 SELECT 3, 'Tuesday' AS weekdayname, 0 AS like_number, 0 AS share_number UNION
 SELECT 4, 'Wednesday' AS weekdayname, 0 AS like_number, 0 AS share_number UNION
 SELECT 5, 'Thursday' AS weekdayname, 0 AS like_number, 0 AS share_number UNION
 SELECT 6, 'Friday' AS weekdayname, 0 AS like_number, 0 AS share_number UNION
 SELECT 7, 'Saturday' AS weekdayname, 0 AS like_number, 0 AS share_number UNION
 SELECT DATEPART(dw,selfie_upload) AS weekdaynum, DATENAME(dw,selfie_upload) AS weekdayname,
 ISNULL(SUM(like_number),0) AS like_number,
 ISNULL(SUM(share_number),0) AS share_number
 FROM selfie
 GROUP BY DATEPART(dw,selfie_upload), DATENAME(dw,selfie_upload)
) AS hasil GROUP BY weekdaynum, weekdayname
ORDER BY weekdaynum, weekdayname

1 | Sunday    | 0   | 0
2 | Monday    | 4   | 4
3 | Tuesday   | 100 | 90
4 | Wednesday | 0   | 0
5 | Thursday  | 160 | 81
6 | Friday    | 0   | 0
7 | Saturday  | 0   | 0