Hi,

i have 3 month of record in my table. if i pass 2 and 10, i need to select the record of between 2 and 10 days of record of every month. if i pass 10 and 20, it should select the record between 10 and 20 of every month.

how to query for that?

Regards

Kasim

Hi,

CREATe Table #Temp (Number NVARCHAR(10),InvDate DATE,Amount DECIMAL(18,3))
insert into #Temp  VALUES ('1001','2015-01-01',100)
insert into #Temp VALUES ('1002','2015-01-03',200)
insert into #Temp VALUES ('1003','2015-01-05',300)
insert into #Temp VALUES ('1004','2015-01-10',400)
insert into #Temp VALUES ('1005','2015-01-12',500)
insert into #Temp VALUES ('1006','2015-01-18',600)
insert into #Temp VALUES ('1007','2015-01-20',700)
insert into #Temp VALUES ('1008','2015-01-21',800)
insert into #Temp VALUES ('1009','2015-01-27',900)

insert into #Temp  VALUES ('1011','2015-02-01',1100)
insert into #Temp VALUES ('1012','2015-02-03',1000)
insert into #Temp VALUES ('1013','2015-02-05',1300)
insert into #Temp VALUES ('1014','2015-02-10',1400)
insert into #Temp VALUES ('1015','2015-02-12',1500)
insert into #Temp VALUES ('1016','2015-02-18',1600)
insert into #Temp VALUES ('1017','2015-02-20',1700)
insert into #Temp VALUES ('1018','2015-02-21',1800)
insert into #Temp VALUES ('1019','2015-02-27',1900)

insert into #Temp  VALUES ('1021','2015-03-01',2100)
insert into #Temp VALUES ('1022','2015-03-03',2200)
insert into #Temp VALUES ('1023','2015-03-05',2300)
insert into #Temp VALUES ('1024','2015-03-10',2400)
insert into #Temp VALUES ('1025','2015-03-12',2500)
insert into #Temp VALUES ('1026','2015-03-18',2600)
insert into #Temp VALUES ('1027','2015-03-20',2700)
insert into #Temp VALUES ('1028','2015-03-21',2800)
insert into #Temp VALUES ('1029','2015-03-27',2900)

DECLARE @p1 int,@p2 int
SET @p1 = 10
SET @p2 = 20
SELECT * FROM #temp WHERE DATEPART(day,InvDate) BETWEEN @p1 and @p2

But you can't use index in this query and it can take long. You can add calculated column in you table with default value DATEPART(DAY,YourDate) and create index on it for performance.

• Edited by Vitaliy Lukashev 11 minutes ago

Hello - You need to use date comparison to achieve this, for sample, see below:

-- Some Data Preparation
declare @tblData TABLE ( Record_Date DATETIME, SalesOrder INT )
INSERT INTO @tblData SELECT '2015-06-01', 1
INSERT INTO @tblData SELECT '2015-06-02', 2
INSERT INTO @tblData SELECT '2015-06-03', 3
INSERT INTO @tblData SELECT '2015-06-04', 4
INSERT INTO @tblData SELECT '2015-06-05', 5
INSERT INTO @tblData SELECT '2015-06-07', 6
INSERT INTO @tblData SELECT '2015-06-10', 7
INSERT INTO @tblData SELECT '2015-06-11', 8
INSERT INTO @tblData SELECT '2015-06-12', 9
INSERT INTO @tblData SELECT '2015-06-14', 10
INSERT INTO @tblData SELECT '2015-06-18', 11
INSERT INTO @tblData SELECT '2015-06-22', 12

-- Test Selections
-- Displaying records between 1 & 10
SELECT * FROM @tblData WHERE Record_Date BETWEEN '2015-06-01' AND '2015-06-10'
-- Displaying records between 10 & 20
SELECT * FROM @tblData WHERE Record_Date BETWEEN '2015-06-10' AND '2015-06-25'

Hope this helps !

Hi,

i have the below table script

CREATe Table #Temp (Number NVARCHAR(10),InvDate DATE,Amount DECIMAL(18,3))
insert into #Temp  VALUES ('1001','2015-01-01',100)
insert into #Temp VALUES ('1002','2015-01-03',200)
insert into #Temp VALUES ('1003','2015-01-05',300)
insert into #Temp VALUES ('1004','2015-01-10',400)
insert into #Temp VALUES ('1005','2015-01-12',500)
insert into #Temp VALUES ('1006','2015-01-18',600)
insert into #Temp VALUES ('1007','2015-01-20',700)
insert into #Temp VALUES ('1008','2015-01-21',800)
insert into #Temp VALUES ('1009','2015-01-27',900)

insert into #Temp  VALUES ('1011','2015-02-01',1100)
insert into #Temp VALUES ('1012','2015-02-03',1000)
insert into #Temp VALUES ('1013','2015-02-05',1300)
insert into #Temp VALUES ('1014','2015-02-10',1400)
insert into #Temp VALUES ('1015','2015-02-12',1500)
insert into #Temp VALUES ('1016','2015-02-18',1600)
insert into #Temp VALUES ('1017','2015-02-20',1700)
insert into #Temp VALUES ('1018','2015-02-21',1800)
insert into #Temp VALUES ('1019','2015-02-27',1900)

insert into #Temp  VALUES ('1021','2015-03-01',2100)
insert into #Temp VALUES ('1022','2015-03-03',2200)
insert into #Temp VALUES ('1023','2015-03-05',2300)
insert into #Temp VALUES ('1024','2015-03-10',2400)
insert into #Temp VALUES ('1025','2015-03-12',2500)
insert into #Temp VALUES ('1026','2015-03-18',2600)
insert into #Temp VALUES ('1027','2015-03-20',2700)
insert into #Temp VALUES ('1028','2015-03-21',2800)
insert into #Temp VALUES ('1029','2015-03-27',2900)

if i pass 3 and 12 the result would be

if i pass 18 and 21 the result would be

hope you understand

thanks 

kasim

Hello - Thanks for DDL, so now you need to work with DATEPART( DAY, <DateCol> ) to address your query see sample below:

-- First Sample Selection
SELECT * FROM #Temp WHERE DATEPART( DAY, InvDate ) BETWEEN 3 AND 12

-- Second Sample Selection
SELECT * FROM #Temp WHERE DATEPART( DAY, InvDate ) BETWEEN 18 AND 21

Hope this helps !

Hi kapoor,

it select the record of single month. what i need is select data of every month. you have mentioned only single month.

Regards

Kasim

you do like this

CREATe Table #Temp (Number NVARCHAR(10),InvDate DATE,Amount DECIMAL(18,3))
insert into #Temp  VALUES ('1001','2015-01-01',100)
insert into #Temp VALUES ('1002','2015-01-03',200)
insert into #Temp VALUES ('1003','2015-01-05',300)
insert into #Temp VALUES ('1004','2015-01-10',400)
insert into #Temp VALUES ('1005','2015-01-12',500)
insert into #Temp VALUES ('1006','2015-01-18',600)
insert into #Temp VALUES ('1007','2015-01-20',700)
insert into #Temp VALUES ('1008','2015-01-21',800)
insert into #Temp VALUES ('1009','2015-01-27',900)

insert into #Temp  VALUES ('1011','2015-02-01',1100)
insert into #Temp VALUES ('1012','2015-02-03',1000)
insert into #Temp VALUES ('1013','2015-02-05',1300)
insert into #Temp VALUES ('1014','2015-02-10',1400)
insert into #Temp VALUES ('1015','2015-02-12',1500)
insert into #Temp VALUES ('1016','2015-02-18',1600)
insert into #Temp VALUES ('1017','2015-02-20',1700)
insert into #Temp VALUES ('1018','2015-02-21',1800)
insert into #Temp VALUES ('1019','2015-02-27',1900)

insert into #Temp  VALUES ('1021','2015-03-01',2100)
insert into #Temp VALUES ('1022','2015-03-03',2200)
insert into #Temp VALUES ('1023','2015-03-05',2300)
insert into #Temp VALUES ('1024','2015-03-10',2400)
insert into #Temp VALUES ('1025','2015-03-12',2500)
insert into #Temp VALUES ('1026','2015-03-18',2600)
insert into #Temp VALUES ('1027','2015-03-20',2700)
insert into #Temp VALUES ('1028','2015-03-21',2800)
insert into #Temp VALUES ('1029','2015-03-27',2900)

DECLARE @Start int = 3
DECLARE @End int = 12

SELECT *
FROM #temp
WHERE InvDate>= DATEADD(mm,DATEDIFF(mm,0,InvDate),@Start -1)
AND InvDate < DATEADD(mm,DATEDIFF(mm,0,InvDate),@End)