Hi,
I want to fetch all bills between two dates but my query doesn't seem to work. It is showing me all the records. Please help me.
My SQL query:
select distinct(bno), name, convert(varchar(50), cdate, 105) as 'Date', CAST(isnull(disc, '0') AS DECIMAL(18,2)) as 'Discount', totalgst, case when pymtmode = 'Cash' then CAST(paidamt AS DECIMAL(18,2)) end as 'Cash', case when pymtmode = 'Credit' then CAST(paidamt AS DECIMAL(18,2)) end as 'Credit', CAST(IsNULL(paidamt, '0') AS DECIMAL(18,2)) as 'GT' from Sales where convert(varchar(50), cdate, 105) BETWEEN CONVERT(varchar(50),'01-01-2022',103) AND CONVERT(varchar(50),'20-03-2022',103) and bno!='0' group by bno, name, cdate, totalgst, disc, paidamt, pymtmode;
Here is the table script with data:
CREATE TABLE [dbo].[Sales](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[bno] [bigint] NOT NULL,
[cdate] [varchar](50) NULL,
[ctime] [varchar](50) NULL,
[name] [varchar](250) NULL,
[mobileno] [varchar](50) NULL,
[address] [varchar](max) NULL,
[gstno] [varchar](50) NULL,
[pan] [varchar](50) NULL,
[description] [varchar](max) NULL,
[hsn] [varchar](50) NULL,
[qtty] [varchar](50) NULL,
[rate] [decimal](18, 2) NULL,
[disc] [decimal](18, 2) NULL,
[sgst] [decimal](18, 2) NULL,
[cgst] [decimal](18, 2) NULL,
[igst] [decimal](18, 2) NULL,
[linetotal] [decimal](18, 2) NULL,
[totalgst] [decimal](18, 2) NULL,
[grsamt] [decimal](18, 2) NULL,
[netamt] [decimal](18, 2) NULL,
[payableamt] [decimal](18, 2) NULL,
[paidamt] [decimal](18, 2) NULL,
[balamt] [decimal](18, 2) NULL,
[status] [varchar](2) NULL,
[rem] [varchar](2) NULL,
[FinancialYear] [varchar](20) NULL,
[pymtstatus] [varchar](2) NULL,
[pymtmode] [varchar](100) NULL,
CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Sales] ON
GO
INSERT [dbo].[Sales] ([id], [bno], [cdate], [ctime], [name], [mobileno], [address], [gstno], [pan], [description], [hsn], [qtty], [rate], [disc], [sgst], [cgst], [igst], [linetotal], [totalgst], [grsamt], [netamt], [payableamt], [paidamt], [balamt], [status], [rem], [FinancialYear], [pymtstatus], [pymtmode]) VALUES (1, 1, N'27-09-2021', N'04:14:06 PM', N'NA', N'', N'NA', N'NA', N'NA', N'DORMEX', N'NA', N'1', CAST(420.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), NULL, CAST(420.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(420.00 AS Decimal(18, 2)), CAST(420.00 AS Decimal(18, 2)), CAST(420.00 AS Decimal(18, 2)), CAST(420.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), N'1', N'0', N'2019-2020', N'1', N'Cash')
INSERT [dbo].[Sales] ([id], [bno], [cdate], [ctime], [name], [mobileno], [address], [gstno], [pan], [description], [hsn], [qtty], [rate], [disc], [sgst], [cgst], [igst], [linetotal], [totalgst], [grsamt], [netamt], [payableamt], [paidamt], [balamt], [status], [rem], [FinancialYear], [pymtstatus], [pymtmode]) VALUES (2, 2, N'03-10-2021', N'09:55:47 AM', N'test-1', N'0000000000', N'NA', N'NA', N'NA', N'ETHEPHON 1LTR', N'', N'3', CAST(850.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), NULL, CAST(2550.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(2790.00 AS Decimal(18, 2)), CAST(2790.00 AS Decimal(18, 2)), CAST(2910.00 AS Decimal(18, 2)), CAST(2910.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), N'1', N'0', N'2019-2020', N'1', N'Cash')
GO
SET IDENTITY_INSERT [dbo].[Sales] OFF
GO
help me correct my query if there is a mistake. Thank you.
