-1

I am trying to find submitted data based on the start and end range entered.

Suppose i have submitted the data today, 30th june

When I give the range as start date, 1 june and end date, 30th june I dont get today's submitted data. When i give start date, 1 june and end date, 1st july I get today's submitted data. How to include start and end date too in the where clause? 

AS
BEGIN
    @Year navchar(200) = null
    @Rtype navchar(200) = = null

    SELECT  *
    FROM ProjectDetails 
    where SubmittedDate Between @Year and @Rtype

END
beginner
  • 303
  • 1
  • 10
  • 31
  • 2
    Possible duplicate of https://stackoverflow.com/questions/16347649/sql-between-not-inclusive – Praveen Jun 30 '17 at 08:47
  • 7
    Possible duplicate of [SQL "between" not inclusive](https://stackoverflow.com/questions/16347649/sql-between-not-inclusive) – Praveen Jun 30 '17 at 08:47

2 Answers2

0

This is because you're misunderstanding how dates work. This is further evidenced by the fact that you're passing and using your dates as strings

First off, dates internally to sql server are expressed as numbers of days since a certain date (midnight on 1/1/1900)

This means a date of 2 jan 1900 is internally expressed by sqlserver as 1.0. A date of 1800 hours on 2 jan 1900 is expressed internally as 1.75 - because there have been 1.75 days since midnight on the first. 6pm is three quarters the way through a 24 hour day hence the .75 part in the internal representation

With me so far?

it is the decimal point part that is defeating your logic

Let's see an example

Lets say you want records between 1 jan 1900 and 10 jan 1900, so really, you want records that have a time anything up to 23:59:59 (and 999999... milliseconds) on 10 jan 1900

This means you want records that are between 0.0 and 9.999999999 days after midnight on 1 jan....

But when you're running your query, you're just asking for:

BETWEEN #1 jan 1900# and #10 jan 1900#

In SQL terms this is

BETWEEN 0.0 and 9.0

And not what you want:

BETWEEN 0.0 and 9.9999999999999999999999999....

Ultimately, midnight is the very first thing that happens on a day. You won't get any records for 6am on the 10th jan because that is WELL AFTER midnight, it is a decimal number like 9.25

I'm sure you can appreciate that 9.25 is NOT BETWEEN 0.0 and 9.0

You will however get records that occurred exactly bang on midnight on the 10th, because they would internally be represented as 9.0, and 9.0 is between 0.0 and 9.0

So you need to alter the way you are doing your query:

date >= startdate AND date < enddate_plus_one_day

i.e. in internal date representation tersm if you want yo get the dates that are 6am, i.e. the 9.25 days after 1 jan 1900, then you need to be querying date >= 0.0 and date < 10.0 - this will return the records all the way up to and including 23:59:59

I also complained at your querying style - youre passing dates as strings and hoping that sqlserver will figure you out. Don't do this. Be explicit:

SELECT  *
FROM ProjectDetails 
where SubmittedDate >= CONVERT(datetime, @Year, 112)--pass date as string in yyyymmdd
  AND SubmittedDate < (CONVERT(datetime, @Rtype, 112)+1.0) --pass date as a string in yyyymmdd
Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • I tried something like this from the above duplicate question link given and its working. Thanks a lot for the explanation you gave. – beginner Jun 30 '17 at 09:08
  • SELECT * FROM Cases WHERE cast(created_at as date) BETWEEN '2013-05-01' AND '2013-05-01' – beginner Jun 30 '17 at 09:08
  • That's working because it's stripping the time part of the date off, so your 9.25 date (6am on the 10th of jan) is becoming 9.0 and it's working... The thing is you're then asking sqlserver to strip the date off maybe millions of rows before doing the comparison. You can save it the time, make your program better by appreciating how the dates work and asking it for a range of "greater than 0.0 and less than 10.0". **Always try to avoid manipulating the data in a table millions of times if you can manipulate the way you write your query just once** – Caius Jard Jun 30 '17 at 09:13
-1
 - --Convert The Date And check The Result Output 
          DECLARE 
              @Year NVARCHAR(200) = null,
               @Rtype NVARCHAR(200)  = null

               SELECT  *
               FROM ProjectDetails 
               WHERE CONVERT(NVARCHAR, SubmittedDate,111)   >= CONVERT(NVARCHAR,@Year,111) AND CONVERT(NVARCHAR,
   SubmittedDate,111)<= CONVERT(NVARCHAR,@Rtype,111)
Alfaiz Ahmed
  • 1,698
  • 1
  • 11
  • 17
  • And what if there are millions of rows in ProjectDetails? You're going to convert all those dates to strings and then perform a string comparison when a number one without conversion of data is possible? Converting data needlessly is awful. Comparing numerical thins using string representations of them is awful and a huge bug risk. This is a terrible way to write programs, sorry for the downvote, but you're doing some **awful** teaching of a beginner here.. – Caius Jard Jun 30 '17 at 09:16
  • @CaiusJard Well if you see the Parameter It's In String.Then how you will fetch The Data from a column which Might be In Datetime Format Can you Show me how you solve this.. – Alfaiz Ahmed Jun 30 '17 at 09:21
  • You convert the string parameter to a date, so sqlserver doesnt convert anything in the table, and you also adjust the range of your query so sqlserver doesnt have to remove the floating point (time) aspect of datetimes.. compare these: `SELECT * FROM milliontable WHERE CAST(datecol as string) = @yymmddstring` versus `SELECT * FROM milliontable WHERE datecol = CAST(@yymmddstring as date)`... In the first, sqlserver might convert millions of dates to string and then do string compare (much slower than number compare), in the second we convert one string parameter to number just once.. – Caius Jard Jun 30 '17 at 09:25
  • @CaiusJard So you are Saying to convert The Parameter to Datetime.then don't you think while fetching the Record from Table will Not give you accurate Data.In your Query I can See Even you are trying to convert into String And Fetch The Data – Alfaiz Ahmed Jun 30 '17 at 09:29
  • If you take a look at my answer I discuss how dates are represented in SQLserver; theyre a floating point number of the number of days since some point in history. If you know you want all records between two datetimes, then you ask sqlserver for the range. If you asked SQLserver for all rows `WHERE number >= 100 and number < 200` and it gave you 99, or 201 I'd be filing a bug with microsoft right away.. But if those numbers were strings and the range was '1' to '9' you'd easily get '8000'. This is why doing numerical comparisons using strings that look like numbers is bad – Caius Jard Jun 30 '17 at 09:33
  • @CaiusJard I really don't Understand What you really wan't to Prove here – Alfaiz Ahmed Jun 30 '17 at 09:41
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/148038/discussion-between-alfaiz-ahmed-and-caius-jard). – Alfaiz Ahmed Jun 30 '17 at 09:44