Get the data of each day in the SQL table in the SQL table. If there is no record data of this day in the table, it will

wingseeker

Table A
id value date
1 aa 2017-01-02
2 bb 2017-01-01
3 cc 2017-01-04

Now want to get the following records from 2017-01-02 to 2017-01-05

value date
aa 2017-01-02
0 2017-01-03
cc 2017-01-04
o 2017-01-05

Can the SQL query be completed? Thank you for your advice.

恶魔右指

select * from table where convert (varchar (8), date, 112) between '20170102'and' '20170105'

恶魔右指

select * from table where convert (varchar (10), date, 120) between '2017-01-02' and '' 2017-01-05 '
Or add hours
select * from table where date between '2017-01-02 00: 00: 00'and' '2017-01-05 23:59:59'

prajna

If he wants to have no date, also list,恶魔右指did not see clearly. .

wingseeker

Yes, the dates that do n’t have to be listed

fanfan1980

create table T (id int, value varchar (10), [date] datetime)
insert T select 1, 'aa', '2017-01-02'
union all select 2, 'bb', '2017-01-01'
union all select 3, 'cc', '2017-01-04'

select isnull (B.value, '0') as value, A. * from
(
select [Date] = '2017-01-02'
union all select '2017-01-03'
union all select '2017-01-04'
union all select '2017-01-05'
) as A
left join T as B on A. [Date] = B. [Date]

--result
value Date
---------- ----------
aa 2017-01-02
0 2017-01-03
cc 2017-01-04
0 2017-01-05

(4 row (s) affected)

恶魔右指

create table ta (id int, value varchar (5), date datetime)
insert ta
select 1, 'aa', '2017-01-02'
union all select 2, 'bb', '2017-01-01'
union all select 3, 'cc', '2017-01-04'

Generate a time period column:
declare @ta table (t_date datetime)
declare @i datetime
set @ i = '2017-01-01 00:00:00'
while @i!> '2017-1-31 00:00:00'
begin
insert @ta select convert (varchar (10), @ i, 120)
select @ i = @ i + 1
end

select time = convert (varchar (10), t_date, 120), value = isnull (value, 0) from @ta a, ta
where convert (varchar (10), t_date, 120) * = convert (varchar (10), date, 120)
and convert (varchar (10), t_date, 120) between '2017-01-02' and '2017-01-05'

Time value
---------- -----
2017-01-02 aa
2017-01-03 0
2017-01-04 cc
2017-01-05 0

(4 rows affected)

恶魔右指

@ta generates a month's date table for the table variable, used with the following statement, there is no need to generate a temporary table

wingseeker

Thanks anyway! What I want is any time period.

wingseeker

Oh, thanks! !! !! !!