You must Sign In to post a response.
  • Category: SQL Server

    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
    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
    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
    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
    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
    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
    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
    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
    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
    Mr.Naveen

    Reporting field datatype is Varchar (max).

    Paul.S

  • #765864
    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
    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
    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
    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
    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


Sign In to post your comments