Fetch records between two dates in SQL Server

jmprateek
 
on Aug 20, 2022 01:21 AM
754 Views

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.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
PrinceG
 
on Aug 22, 2022 01:15 AM

Hi jmprateek,

Use Greater and Less than in Date column for fetching the records between two dates.

Please refer below updated query.

SOL

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) >= CONVERT(varchar(50),'03-10-2021',103) 
	AND CONVERT(varchar(50), cdate, 105) < CONVERT(varchar(50),'07-01-2022',103) and bno!='0'
group by bno, name, cdate, totalgst, disc, paidamt, pymtmode;

Screenshot