Introduction
In this post, I am going to explain the SQL statements which are used to to get the date and time difference in a valid format; for example, one hour ago, 30 minutes ago, one week ago, one month ago, and so on.
Implementation
When you work with any data-driven application, sometimes, you need to fetch the date and time from the database and display in your Web/Windows form based on your requirement. So today, I will show you the proper way and format to get/fetch the date and time from your database and display it in a proper manner.
First, I will create one SQL function that will return the string with the date and time.
SQL Function
- CREATE FUNCTION GetDateFormat
- (
- @FROM_DATE DATETIME ,
- @TO_DATE DATETIME
-
- )
- RETURNS
- VARCHAR(100)
- AS
- BEGIN
- DECLARE @Date
- AS
- VARCHAR(100)
- SELECT @Date = CASE WHEN DATEDIFF(mi, @FROM_DATE, @TO_DATE) <= 1
- THEN'1 min ago'
- WHEN DATEDIFF(mi, @FROM_DATE, @TO_DATE) > 1 AND DATEDIFF(mi, @FROM_DATE, @TO_DATE) <= 60 THEN
- CONVERT(VARCHAR, DATEDIFF(mi, @FROM_DATE, @TO_DATE)) + ' mins ago'WHEN DATEDIFF(hh, @FROM_DATE, @TO_DATE) <= 1 THEN
- CONVERT(VARCHAR, DATEDIFF(hh, @FROM_DATE, @TO_DATE)) + ' hour ago'WHEN DATEDIFF(hh, @FROM_DATE, @TO_DATE) > 1 AND DATEDIFF(hh, @FROM_DATE, @TO_DATE) <= 24 THEN
- CONVERT(VARCHAR, DATEDIFF(hh, @FROM_DATE, @TO_DATE)) + ' hrs ago'WHEN DATEDIFF(dd, @FROM_DATE, @TO_DATE) <= 1 THEN
- CONVERT(VARCHAR, DATEDIFF(dd, @FROM_DATE, @TO_DATE)) + ' day ago'WHEN DATEDIFF(dd, @FROM_DATE, @TO_DATE) > 1 AND DATEDIFF(dd, @FROM_DATE, @TO_DATE) <= 7 THEN
- CONVERT(VARCHAR, DATEDIFF(dd, @FROM_DATE, @TO_DATE)) + ' days ago'WHEN DATEDIFF(ww, @FROM_DATE, @TO_DATE) <= 1 THEN
- CONVERT(VARCHAR, DATEDIFF(ww, @FROM_DATE, @TO_DATE)) + ' week ago'WHEN DATEDIFF(ww, @FROM_DATE, @TO_DATE) > 1 AND DATEDIFF(ww, @FROM_DATE, @TO_DATE) <= 4 THEN
- CONVERT(VARCHAR, DATEDIFF(ww, @FROM_DATE, @TO_DATE)) + ' weeks ago'WHEN DATEDIFF(mm, @FROM_DATE, @TO_DATE) <= 1 THEN
- CONVERT(VARCHAR, DATEDIFF(mm, @FROM_DATE, @TO_DATE)) + ' month ago'WHEN DATEDIFF(mm, @FROM_DATE, @TO_DATE) > 1 AND DATEDIFF(mm, @FROM_DATE, @TO_DATE) <= 12 THEN
- CONVERT(VARCHAR, DATEDIFF(mm, @FROM_DATE, @TO_DATE)) + ' mnths ago'WHEN DATEDIFF(yy, @FROM_DATE, @TO_DATE) <= 1 THEN
- CONVERT(VARCHAR, DATEDIFF(yy, @FROM_DATE, @TO_DATE)) + ' year ago'WHEN DATEDIFF(yy, @FROM_DATE, @TO_DATE) > 1 THEN
- CONVERT(VARCHAR, DATEDIFF(yy, @FROM_DATE, @TO_DATE)) + ' yrs ago'END
- RETURN @Date
- END
Now, let us see how you can use this SQL function. You just need to pass the "from" and "to" date in the created function as parameters.
- DECLARE @FromDate DATETIME
- SET @FromDate ='2018-08-22 11:48:49.830'
- select dbo.GetDateFormat(@FromDate,GETDATE()) [Date]