-1

I need help how to get the number of day(s) difference in SQL query. Comparisons are made between one date column (Billing Date) with numerous, different value and one date value only (Date Generated).

Values:

Date Generated: 09/13/2022

BillDate
2022-01-03            
2022-02-03
2022-03-05
2022-04-05
2022-05-05
2022-06-03
2022-07-04
2022-08-03
2022-09-02
2022-10-03

I have this query so far:

Declare @DateGen as date
SET @DateGen = '2022-09-13'

DECLARE @BillDateTable TABLE (BillDate date)
insert into @BillDateTable (BillDate) SELECT ReaDate FROM Ledger 

SELECT DATEDIFF(DAY, (SELECT BillDate FROM @BillDateTable), @DateGen)

And I get this error:

Msg 512, Level 16, State 1, Line 14 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.

Dale K
  • 25,246
  • 15
  • 42
  • 71
bordeaux
  • 1

1 Answers1

0

Try this code,

DECLARE @LedgerTable TABLE (ReaDate date ) insert into @LedgerTable (ReaDate) values ('01/03/2022'),
('02/03/2022'), ('03/05/2022'), ('04/05/2022'), ('05/05/2022'), ('06/03/2022'), ('07/04/2022'), ('08/03/2022'), ('09/02/2022'), ('10/03/2022')

Declare @DateGen as date SET @DateGen = convert (date, '01/12/2023',103) DECLARE @BillDateTable TABLE (BillDate date,no_of_days int )

INSERT INTO @BillDateTable (BillDate,no_of_days)

SELECT convert (date, ReaDate,103), DATEDIFF (DAY, convert (date, ReaDate,103) , @DateGen) as no_of_days FROM @LedgerTable

select * from @BillDateTable

Prabhakar Anand
  • 7
  • 2