How to find Minimum and Maximum of Consecutive Dates in SQL server

When we have a condition to find the min and max of date from two different consecutive date columns from the same table with different id as shown below.

Query:- Select * from Project

O/P:-     

Id	StartDate	EndDate
8	2015-10-01	2015-10-02
9	2015-10-02	2015-10-03
10	2015-10-03	2015-10-04
11	2015-10-13	2015-10-14
12	2015-10-14	2015-10-15
13	2015-10-28	2015-10-29
14	2015-10-30	2015-10-31

Now we have the condition is to find Min and Max from these consecutive dates

Query:- WITH set1 AS (

                                                SELECT dd dt,ROW_NUMBER() OVER(ORDER BY dd) Rnum

                                                FROM

                                                (select startdate as dd from Project

                                                union select enddate  as dd from Project

                                                ) fnl

                                                GROUP BY dd

                                        )

SELECT MIN(dt) mindt,MAX(dt) maxdt

FROM set1

GROUP BY DATEDIFF(day,Rnum,dt)

 O/P:-

Mindt	Maxdt
2015-10-01	2015-10-04
2015-10-13	2015-10-15
2015-10-28	2015-10-31

 

What other ideas can you add to this post that I may have not mentioned?

How to remove extra space between the string in SQL Server Database

We get such type of condition when we concatenate multiple columns data into one output column and those column data have extra space.

For E.g: We have declared one variable and entered some dummy data as shown below.

Query:-

DECLARE @TestString1 AS VARCHAR(100)

SET @TestString1 = 'Address 1,      Address2,    Address3,     123'

print @TestString1;

Output:-   Address 1,      Address2,    Address3,     123

Here we are getting extra space between the addresses and condition to remove the extra space between the string then

Query:-

DECLARE @TestString2 AS VARCHAR(100)

SET @TestString2 = 'Address 1,      Address2,    Address3,     123'

SET @TestString2= REPLACE(REPLACE(REPLACE(@TestString2,' ',' %'),'% ',''),'%','')

print @TestString2;

Output:- Address 1, Address2, Address3, 123

In this query, we have replaced the extra space through a single space with the help of a wildcard operator.

What other ideas can you add to this post that I may have not mentioned?

Date and Time Formats in MS SQL Server Database

Convert() is basically used to convert any value in specified data type, Getdate() is used to fetch the date and time from the server where the database is stored. Queries to fetch date and time from database in a different format is shown below

1.  SELECT GETDATE() AS [Result] -- 2020-01-07 02:44:17.323

2.  SELECT CONVERT(VARCHAR(30),GETDATE(),100) AS [Result] -- Jan  7 2020  2:44AM

3.  SELECT CONVERT(VARCHAR(30),GETDATE(),101) AS [Result] -- 01/07/2020

4.  SELECT CONVERT(VARCHAR(30),GETDATE(),102) AS [Result] -- 2020.01.07

5.  SELECT CONVERT(VARCHAR(30),GETDATE(),103) AS [Result] -- 07/01/2020

6.  SELECT CONVERT(VARCHAR(30),GETDATE(),104) AS [Result] -- 07.01.2020

7.  SELECT CONVERT(VARCHAR(30),GETDATE(),105) AS [Result] -- 07-01-2020

8.  SELECT CONVERT(VARCHAR(30),GETDATE(),106) AS [Result] -- 07 Jan 2020

9.  SELECT CONVERT(VARCHAR(30),GETDATE(),107) AS [Result] -- Jan 07, 2020

10. SELECT CONVERT(VARCHAR(30),GETDATE(),108) AS [Result] -- 02:45:38

11. SELECT CONVERT(VARCHAR(30),GETDATE(),109) AS [Result] -- Jan  7 2020  2:45:44:427AM

12. SELECT CONVERT(VARCHAR(30),GETDATE(),110) AS [Result] -- 01-07-2020

13. SELECT CONVERT(VARCHAR(30),GETDATE(),111) AS [Result] -- 2020/01/07

14. SELECT CONVERT(VARCHAR(30),GETDATE(),112) AS [Result] -- 20200107

15. SELECT CONVERT(VARCHAR(30),GETDATE(),113) AS [Result] -- 07 Jan 2020 02:46:14:633

16. SELECT CONVERT(VARCHAR(30),GETDATE(),114) AS [Result] -- 02:46:19:930

17. SELECT CONVERT(VARCHAR(30),GETDATE(),120) AS [Result] -- 2020-01-07 02:46:25

18. SELECT CONVERT(VARCHAR(30),GETDATE(),121) AS [Result] -- 2020-01-07 02:46:30.080

19. SELECT CONVERT(VARCHAR(30),GETDATE(),126) AS [Result] -- 2020-01-07T02:46:43.200

20. SELECT CONVERT(VARCHAR(30),GETDATE(),127) AS [Result] -- 2020-01-07T02:46:49.050

21. SELECT CONVERT(VARCHAR(30),GETDATE(),130) AS [Result] -- 12 ????? ?????? 1441  2:47:14:

22. SELECT CONVERT(VARCHAR(30),GETDATE(),131) AS [Result] -- 12/05/1441  2:47:25:077AM

23. SELECT DATENAME(MONTH, GETDATE())

    + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) 

                                          AS [Result] -- March 09,  2022 

What other ideas can you add to this post that I may have not mentioned?