Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to insert a date range parameter
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

3 Posts

Posted - 09/16/2013 :  12:07:01  Show Profile  Reply with Quote
I am new to SQL. I am pulling information from my POS system which took some time to figure out. I am trying to pull MTD and YTD sales for this year and last year.

I have this line to pull all invoices between two dates:
WHERE (invc_hdr.ivh_dt Between '08/01/2013' And '08/31/13')
But I want to be able to put the dates in without having to change the code each time. I want a window to pop up so I can input the date range. Is there a way to do this.

Any help would be great.

Edited by - Lumber on 09/16/2013 14:06:41

Patron Saint of Lost Yaks

30421 Posts

Posted - 09/16/2013 :  14:52:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
1) Write the code as a stored procedure
2) Write the code as a inline table valued function

CREATE FUNCTION dbo.fnMyFunction
	@FromDate DATE,
	@ToDate DATE
		SELECT	...
		FROM	...
		WHERE	invc_hdr.ivh_dt BETWEEN @FromDate AND @ToDate

FROM	dbo.fnMyFunction('20130801', '20130814');

Go to Top of Page

Starting Member

3 Posts

Posted - 09/16/2013 :  17:05:34  Show Profile  Reply with Quote
I am pulling the data using ODBC. So I am not sure if this will work. I think I am a little over my head.
Go to Top of Page

Starting Member

30 Posts

Posted - 09/19/2013 :  23:34:22  Show Profile  Reply with Quote
This is really easy from a technical perspective.

Look up "form processing" and your language: PHP/ASP/whatever. There will be tons of tutorials on how to handle to do this. You'll be able to copy them almost verbatim except for the SQL. Dates can get a little bit messy, depending on formatting, so you may need to find a validation script of some sort, usually JavaScript.

If this is for mass consumption then you'll probably want to read up on SQL Injection as well.

That sounds like a lot, but seriously, someone's done it all for you, you've just got to find it.

Note: If you are using PHP the process for this has changed over the years so make sure you are looking at a newer tutorial.
Go to Top of Page

Starting Member

3 Posts

Posted - 09/20/2013 :  16:06:59  Show Profile  Reply with Quote
I am pulling info from ECS Pro (our POS system) by writing a SQL code and it is putting the info in excel. My boss wants a spreadsheet that will show MTD and YTD accounting totals for this year and last year. I found the tables I needed. When I use "WHERE (invc_hdr.ivh_dt=?). A window will pop up every time I refresh the spreadsheet and I can put the date in. But I don't want just one date. So I rewrote my code to look like this WHERE (invc_hdr.ivh_dt Between 'mm/dd/yyyy' And mm/dd/yyyy). This will get me the rage I want but I will have to change the date in the code every time I want a different date rage.

Is there just a way to change my WHERE code to have a window pop up but still put in a date range?
Go to Top of Page

Posting Yak Master

249 Posts

Posted - 09/21/2013 :  00:52:12  Show Profile  Reply with Quote
I guess you want where clause input as Between '08/01/2013' And '08/31/13' and then Between '09/01/2013' And '09/30/13') so forth till December for you MTD. If this is the case then you can use CURSOR but inside the cursor you still need use SwePeso's way to solve this.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000