Category: SQL Server

05 May 2016 04:24

Joined:	13/08/2015
Level:	Silver
Points:	102

Points:	5

How To Get Records Between Two Particular(from-two) Dates in Sql Server 2008 r2

I want to get the records between two particular Dates only .

My Using Code is
SELECT reportingdate FROM tbl_instrumentssales WHERE reportingdate >= '07/10/2015'
AND reportingdate<= '22/10/2015'

my code is working fine But it give output someother records Also instead of my reqirement.like below

14/09/2015,
14/09/2015
14/09/2015
14/09/2015
14/09/2015
15/09/2015
15/09/2015
15/09/2015
15/09/2015
15/09/2015
15/09/2015
18/09/2015
18/09/2015
18/09/2015
18/09/2015
12/10/2015
22/02/2016
07/10/2015

If i make any mistake in my query or i have to change anything na please tell me.
else give some example query for i am get records between two dates.
two dates is equal to From date & todate.

Thanking you
Paul.S

#765828

06 May 2016 03:09

Joined:	21/11/2010
Level:	Bronze
Points:	81

Points:	3

first check your Data Type for the Column reportingdate. it should be DateTime
then you can use
SELECT reportingdate FROM tbl_instrumentssales WHERE reportingdate between '2015/10/07' and '2015/10/22'
OR
select reportingdate from tbl_instrumentssales where reportingdate >= '2015/10/07' and reportingdate <= '201/10/22'

#765829

06 May 2016 22:45

Joined:	05/06/2009
Level:	Gold
Points:	10245

Points:	2

You have to convert string into data then try to compare


select * from tbl_instrumentssales  where  reportingdate  >= convert(datetime, '2015/10/07', 120) and reportingdate  <=  convert(datetime, '2015/10/07', 120)

By Nathan
Direction is important than speed

#765837

08 May 2016 23:35

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	3

Hi Paul,

While getting the data between two dates you have to very sure about the datatype of the field, and get the records in between 2 date fields using BETWEEN Keyword.

Ex:


select col1, col2
from tablename
where date1 between @fromdate and @todate

based on your need you have to do more customization on the same.

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#765855

09 May 2016 04:54

Joined:	13/08/2015
Level:	Silver
Points:	102

Points:	1

There is nothing problem on the datatype Mr.Naveen .
that is exactly correct format.
I have tried all the way but my bad luck still i have the same problem . it get the records instead of other records Also.

Thanks For all yours Valuable Replies Friends.
Paul.S

#765856

09 May 2016 05:52

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	3

Hi Paul,

I'm not saying that there is a problem in datatype, i mean to say that for date field we can use different type of datafields like DATE, DATETIME etc... while doing any action in the respective fields you should be convert with appropriate format, that is what i'm saying.

And I'm little bit confused with this, "it get the records instead of other records Also", could you please explain more on the same, so that we can help you better.

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#765858

09 May 2016 21:31

Joined:	13/08/2015
Level:	Silver
Points:	102

Points:	1

Hi. Naveen ,
"it get the records instead of other records Also" Means My query

SELECT reportingdate FROM tbl_instrumentssales WHERE reportingdate >= '07/10/2015'
AND reportingdate<= '22/10/2015' get all records between '07/10/2015' and '22/10/2015' dates.

my query is should be taken only dates between '07/10/2015' and '22/10/2015' dates.
it is taken.

but the query additionally get some other dates records in my sql table.

my query is working fine in my requires dates records . but the problem is that query get additional records.that means not mentioned dates records too.
my sql query is exactly correct so i cant able to find the reason for this type of output.
Paul.S

#765860

09 May 2016 22:57

Joined:	14/04/2016
Level:	Silver
Points:	118

Points:	4

Hi Paul,

In SQL, if you execute your query and it contains between dates and your DateTime has different times, not all relevant data is returned. This is because you didn't specify the correct time for the date time. The default time is 00:00:00.

ID Name CapturedDate
1 Susan 2012-03-27 08:02:45
2 John 2012-03-27 09:14:56
3 James 2012-03-27 10:15:45
4 Clair 2012-03-27 11:45:54
5 Blair 2012-03-28 12:45:48

SELECT ID
FROM TestInfo
WHERE CapturedDate BETWEEN '2012-03-27' AND '2012-03-27'
This query will not return any information, because the default time is 00:00:00. None of the capture dates have a time of 00:00:00. There are different ways to fix this problem.

Solution One

You can type in the correct time.

SELECT ID
FROMTestInfo
WHERE CapturedDate BETWEEN '2012-03-27' AND '2012-03-27 23:59:59'
This will return ID 1,2,3,4, because we are searching for all record between midnight and just before midnight of the next day. This solution can work if u knows that u have to add the time. But sometimes it is impractical to, do this. And this can become a time consuming process. Like when you start using parameters, then the user need to enter the data and to expect the user to add the time can cause a lot of problems.

Solution Two:

You can add the time on to the endDate parameter.

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate = '2012-03-27'
SET @EndDate = '2012-03-27'
SELECT ID
FROM TestInfo
WHERE CapturedDate BETWEEN @StartDate AND @EndDate + ' 23:59:59'
This will return ID 1,2,3,4. This solution resolves the issue that the user needs to enter the time.

Solution Three

You can use the dateadd function of SQL.

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate = '2012-03-27'
SET @EndDate = '2012-03-27'

SELECT ID
FROM TestInfo
WHERE CapturedDate BETWEEN @StartDate AND DATEADD(s,-1,DATEADD(d,1,@EndDate))
This will return ID 1,2,3,4. We are doing a double Dateadd; the first is to add a day to the current endDate, it will be 2012-03-28 00:00:00, then you subtract one second to make the end date 2012-03- 27 23:59:59.

#765862

09 May 2016 23:18

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	1

Hi Paul,

What is datatype of "reportingdate " field?

Please confirm that..

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#765863

09 May 2016 23:44

Joined:	13/08/2015
Level:	Silver
Points:	102

Points:	1

Mr.Naveen

Reporting field datatype is Varchar (max).

Paul.S

#765864

10 May 2016 00:14

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	3

Hi Paul,

Convert both sides dates in same format, if you compare date with character it won't give exact result either you should convert in datetime or date.

Ex:


declare @fromdate,@todate nvarchar(25) 
set @fromdate = '07/10/2015'
set @todate= '22/10/2015'

SELECT reportingdate 
FROM tbl_instrumentssales 
WHERE CONVERT(DATE,  reportingdate) >=CONVERT(DATE,@fromdate)
AND CONVERT(DATE, reportingdate)<= CONVERT(DATE,@todate)

Try something like above to achieve your goal....

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#765865

10 May 2016 03:12

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	4

Hi
try this Query For Date Query



Declare @fromdate nvarchar(25),@todate nvarchar(25) 
set @fromdate = '01/01/2015'
set @todate= '31/01/2016'
SELECT USERID FROM SAMPLE2 WHERE DATE_TIME BETWEEN CONVERT(VARCHAR(20),@FROMDATE,101)  AND CONVERT(VARCHAR(20),@TODATE,101)
OR
SELECT USERID FROM SAMPLE2 WHERE DATE_TIME BETWEEN CONVERT(VARCHAR(20),@FROMDATE,102)  AND CONVERT(VARCHAR(20),@TODATE,102)

OR
SELECT USERID FROM SAMPLE2 WHERE DATE_TIME BETWEEN CONVERT(VARCHAR(20),@FROMDATE,103)  AND CONVERT(VARCHAR(20),@TODATE,103)

OR
SELECT USERID FROM SAMPLE2 WHERE DATE_TIME BETWEEN CONVERT(VARCHAR(20),@FROMDATE,111)  AND CONVERT(VARCHAR(20),@TODATE,111)

Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com

'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

#765978

18 May 2016 00:23

Joined:	29/06/2010
Level:	Silver
Points:	267

Points:	2

Hello,

Please try with the below query :-

select reportingdate from tbl_instrumentssales where reportingdate between '20151007'
and '20151022'

Hope it will solve your problem.

Thanks

#766515

17 Jun 2016 02:17

Joined:	12/12/2013
Level:	Gold
Points:	529

Points:	3

Hi ,

Kindly use the following Query for your question, It may Help you,

SELECT reportingdate FROM tbl_instrumentssales WHERE reportingdate between convert(varchar(12),'07/10/2015',112) and convert(varchar(12),22/10/2015',112)

While Using the Convert() Function for Date means it Quite easy to access or Fetch the records from table..

Regards,
Karunanidhi.K

#766866

28 Jun 2016 10:31

Joined:	20/03/2013
Level:	Gold
Points:	4176

Points:	2

Date format should be same of the first date and the second date if it is not there dateformat should change to particular format and query it.

SRI RAMA PHANI BHUSHAN KAMBHAMPATI

How To Get Records Between Two Particular(from-two) Dates in Sql Server 2008 r2

About dotnetspider.com

Quick Links

General

Services