Select the difference for the number of the latest date and the earliest date by date _mssql

Table structure is as follows

Number Date
8 2009/1/11 2:00
7 2009/1/11 5:00
6 2009/1/11 12:00
5 2009/1/11 18:00
4 2009/1/12 4:00
3 2009/1/12 10:00
2 2009/1/12 12:00
1 2009/1/11 17:00

The difference between the number of the earliest and the latest of the day is the result of the following:

Poor
2
3

Copy Code code as follows:

CREATE TABLE #date
(
Number int identity (1,1) primary key,
Date datetime
)
Insert INTO #date select ' 2009/1/11 17:00 '
Insert INTO #date select ' 2009/1/12 12:00 '
Insert INTO #date select ' 2009/1/12 10:00 '
Insert INTO #date select ' 2009/1/12 4:00 '
Insert INTO #date select ' 2009/1/11 18:00 '
Insert INTO #date select ' 2009/1/11 12:00 '
Insert INTO #date select ' 2009/1/11 5:00 '
Insert INTO #date select ' 2009/1/11 2:00 '

Select (d2.number-d1.number) number
From
(
Select Number,date from #date where date in
(select Max (date) from #date GROUP by CONVERT (varchar), date,120)
) D1
,
(
Select Number,date from #date where date in
(select min (date) from #date GROUP by CONVERT (varchar), date,120)
) D2
where CONVERT (varchar (), d1.date,120) =convert (varchar, d2.date,120)

Number
-----------
2
3