Geeks With Blogs
Marcin Celej blog

I ve been recently creating few reports that required dates as parameters. It is quite common to have a report that must be provided range of dates (from and to). I investigated few reports that was created by my colleagues and I found those reports are not intuitive. Let me explain:

When I see two dates that must be provided to a report I believe that when I provide the same date in from and to parameters it will show all data for the paricular day. See screenshot below:

from-to1

But to achieve this you need to create a query in a specific way. Usually in such case both parameters passed to the query are equal so if you want your report to work in an intuitive way you need to write this kind of SELECT:

SELECT
    MyDate
FROM
    MyTable alias
WHERE
    (alias.MyDate >= @DateFrom) AND
    (alias.MyDate < DATEADD(d, 1, @DateTo))

Here the MyDate should be greater or equal to DateFrom and less to DateTo (but the DateTo is added with one day).

Maybe it's obvious but I found many reports with the bug. developer usually do not care (or do not know) if the report shows what it should show. Baka (stupid in Japanese) developer.

If you want to provide open range of dates: 

  • start with infinite end   from-to2
  • end with no start from-to3
  • no date filtering at all from-to4

use the following snippet:

SELECT
    MyDate
FROM
    MyTable alias
WHERE
    (@DateFrom IS NULL OR alias.MyDate >= @DateFrom) AND
    (@DateTo IS NULL OR alias.MyDate < DATEADD(d, 1, @DateTo))

This blog entry will be useful at least for my colleagues.

Posted on Wednesday, October 31, 2007 9:00 AM | Back to top


Comments on this post: SQL date filtering - between date from and to

# re: SQL date filtering - between date from and to
Requesting Gravatar...
This worked fantastic! Thanks so much.

Sean S,
Left by Sean on Oct 14, 2008 9:11 AM

# re: SQL date filtering - between date from and to
Requesting Gravatar...
This helped me out. Thanks
Left by Hari on Jun 09, 2009 11:42 AM

# re: SQL date filtering - between date from and to
Requesting Gravatar...
Great yaar, thanks for the amazing help .
Left by Abhishek on Jun 18, 2009 11:50 PM

# re: SQL date filtering - between date from and to
Requesting Gravatar...
Awesome. This helped me a lot!
Left by FiLL on Jul 21, 2009 4:52 AM

# re: SQL date filtering - between date from and to
Requesting Gravatar...
a lot fthanks to you, simply superb
Left by karthik on Aug 16, 2009 11:43 PM

# re: SQL date filtering - between date from and to
Requesting Gravatar...
Thanks a lot!
Left by Curro on Feb 23, 2010 9:22 AM

# Data filtering
Requesting Gravatar...
i want to filter my records that contain same name, same date but different amount by just selecting only the name and store it in a dbgrid as one line after calculating the total amount.
Left by Babs on May 09, 2010 10:37 PM

# re: SQL date filtering - between date from and to
Requesting Gravatar...
THANKS SO MUCH....U saved me a whole weeks worth of effort.. God bless u.
Left by Junia on Jul 02, 2010 2:34 AM

# re: SQL date filtering - between date from and to
Requesting Gravatar...
Hi I have a query. I have two dates in database check in date and check out date . on the basis of these dates I have to get the price from database. I set price for those dates. like I set Price for 2011-01-01 to 2011-12-31.

when I retrieve It does not show anything.
SELECT * FROM tariff_infoT WHERE datefrom between '2011-02-02' and '2011-02-05'

I want to get price in any date between dates I have set in the database.

Left by subhash on Jul 12, 2011 9:55 PM

# re: SQL date filtering - between date from and to
Requesting Gravatar...
Witaj Marcin -

twoj kawalek codu bardzo mi pomogl - chcialem podziekowac tutaj za madre rowiazanie - wlasnie sie meczylem z tym problemem budujac reporting w naszej firmie. Wiedzialem ze jest na to sposob, poniewaz w poprzedniej firmie tez bylo to rozwiazane. Dobrze ze znalalzem twoj blog.

Pozdrawiam z Nowego Yorku

Marcin Zietarski
Left by Martin Zietarski on Oct 05, 2011 2:29 AM

# re: SQL date filtering - between date from and to
Requesting Gravatar...
i would like to count the no. of days in the market using sql query :
visit period : 8-Mar-12 to 9-mar-12 : Customer x
09-mar-12 : Customer Y
total no. of days nt he market is : 2 days
how to get the above days using sql query
Left by S Uma on Mar 26, 2012 7:49 PM

# SQL date filtering - between start date from end date
Requesting Gravatar...
Hi,

Please can you help me, to filter the date range using JSP code, the user select to the text box.
Left by Priya on Apr 08, 2012 7:08 PM

# re: SQL date filtering - between date from and to
Requesting Gravatar...
Hi any body help me, to filter the date using a simple sql query
Left by Suvetha on Apr 11, 2012 4:59 PM

# re: SQL date filtering - between date from and to
Requesting Gravatar...
Thx alot... U save me
Left by Aman Narang on Jun 20, 2012 4:18 PM

# re: SQL date filtering - between date from and to
Requesting Gravatar...
Really helpful. Thank you so much.
Left by Mimi on Jul 13, 2012 1:23 AM

# re: SQL date filtering - between date from and to
Requesting Gravatar...
very useful and informative information. thankl you
Left by Packers Movers on Aug 17, 2012 1:03 AM

# re: SQL date filtering - between date from and to
Requesting Gravatar...
Really helpful. Thank you so much.
Packers Movers
Left by Packers Movers on Aug 17, 2012 1:05 AM

# re: SQL date filtering - between date from and to
Requesting Gravatar...
Fantastic, job is a good'n. Life will be easier! Thanks.
Left by Blob on Sep 26, 2012 9:22 PM

# re: SQL date filtering - between date from and to
Requesting Gravatar...
This save me lot of time, and make my other reports easier to implement as they should be the same format, this is the part that i was missing on my query : DATEADD(d, 1, @DateTo)). This queries are very useful. Thanks.
Left by Japhter Tshihatu on Feb 04, 2013 7:47 PM

# re: SQL date filtering - between date from and to
Requesting Gravatar...
Very useful information. Thank you for sharing it. Thanks <a href=” http://www.99th.in”>99th
Left by 99thin on May 28, 2013 9:31 PM

# re: SQL date filtering - between date from and to
Requesting Gravatar...
Packers and Movers Directory is one of the fastest growing packers and movers service provider in India. Packers Movers Directory serve the demand of domestic and international packers movers requirements.
Left by Sourabh on Oct 21, 2013 5:52 PM

# AWESOME LOGIC
Requesting Gravatar...
Very simple and powerful logic
Left by ANWER JAMAL on Mar 01, 2016 12:19 AM

# re: SQL date filtering - between date from and to
Requesting Gravatar...
was wondering if anyone can help me

SELECT
pro.gtrans_items.*, pro.gtrans_defs.*, pro.dsp_cartons.*, pro.loc_defs.loc_sort



FROM pro.gtrans_items, pro.loc_defs, pro.gtrans_defs, pro.dsp_cartons

WHERE pro.gtrans_defs.gsource_num = pro.loc_defs.loc_num
AND pro.gtrans_defs.gtrans_num = pro.gtrans_items.gtrans_num
AND pro.gtrans_items.carton_barcode = pro.dsp_cartons.carton_barcode
AND pro.dsp_cartons.close_date

Does anyone know how i can filter my close_date column by todays date?
Left by Tony on Aug 20, 2016 12:59 AM

Your comment:
 (will show your gravatar)


Copyright © Marcin Celej | Powered by: GeeksWithBlogs.net