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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to insert a date range parameter

Author  Topic 

Lumber
Starting Member

3 Posts

Posted - 2013-09-16 : 12:07:01
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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-16 : 14:52:06
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
)
RETURNS TABLE
AS
RETURN (
SELECT ...
FROM ...
WHERE invc_hdr.ivh_dt BETWEEN @FromDate AND @ToDate
)
GO

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



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Lumber
Starting Member

3 Posts

Posted - 2013-09-16 : 17:05:34
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

erikhaselhofer
Starting Member

30 Posts

Posted - 2013-09-19 : 23:34:22
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

Lumber
Starting Member

3 Posts

Posted - 2013-09-20 : 16:06:59
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

allan8964
Posting Yak Master

249 Posts

Posted - 2013-09-21 : 00:52:12
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
   

- Advertisement -