Displaying data from a certain date range

Hey all, hopefully this question is in the right spot. I'm writing a .NET app talking to a MS SQL 2000 DB. I have two date range input boxes, and I want to display the data (probably in a dataGrid) from those 2 certain dates. How do I go about this with my SQL server??

My DB table has a date field that I would use to search for the data between those two user specified dates. Any tips, examples, etc. would be greatly appreciated!!!Hows about...

USE Northwind
GO

-- Your text boxes
DECLARE @.x datetime, @.y datetime SELECT @.x = '1996-09-01', @.y = '1996-09-30'

SELECT OrderDate, DATEDIFF(d,@.x,OrderDate), DATEDIFF(d,OrderDate,@.y)
FROM Orders
WHERE DATEDIFF(d,@.x,OrderDate) > = 0 AND DATEDIFF(d,OrderDate,@.y) > = 0|||Okay, now whats up with this DECLARE @.x datetime, @.y datetime SELECT @.x = '1996-09-01', @.y = '1996-09-30'

What am I doing with my Date1.text and Date2.text input boxes.. sorry, I'm having trouble converting the code some.|||That's TSQL

The DECALRE and SELECT is to mimic the values in your text box...

Do you have sql server client tools installed?

Query Analyzer?

Your best bet would be to call a stored procedure...|||Yes, I have those tools installed, but I really don't know how to use them to my benefit.... :(|||Puttin this code in the Query analyzer shows the DATEDIFF in pink, and some of the code after that grey... I'm guessing something else needs to be used here?|||Hows about...

USE Northwind
GO

-- Your text boxes
DECLARE @.x datetime, @.y datetime SELECT @.x = '1996-09-01', @.y = '1996-09-30'

SELECT OrderDate, DATEDIFF(d,@.x,OrderDate), DATEDIFF(d,OrderDate,@.y)
FROM Orders
WHERE DATEDIFF(d,@.x,OrderDate) > = 0 AND DATEDIFF(d,OrderDate,@.y) > = 0

Brett, I am having a similar problem...can't seem to use datetime parameters with default values in a stored procedure.

This is the code I used. Basically, trying to default dates on the Begin and End Dates for the query...but when I run it, always get this error
'Syntax error converting datetime from character string.'

Is the problem in the default values or the datediff functions? Then is the solution to format the dates differently or use a cast/convert function?

Thanks for the help

Alex

CREATE PROCEDURE dbo.usp_TempTest
@.BeginDate DateTime = '11/1/2004',
@.EndDate DateTime = getdate

AS

SELECT dbo.vw_BasicAuditDetails_Complete.*
FROM dbo.vw_BasicAuditDetails_Complete
WHERE (DATEDIFF(d, @.BeginDate, Audit_TM) > 0)
AND (DATEDIFF(d, Audit_TM, @.EndDate) > 0)
GO|||Has anyone cut and pasted my code in to query analyzer and executed it?|||That's what I was trying to do earlier.... :confused: I'm not a pro with the analyzer though..|||Well, if you highlighted the text, and pasted it in to a QA (Query Analyzer) window, and the typed [CTRL]+E, the code would execut and give you this

OrderDate
---------------- ---- ----
1996-09-02 00:00:00.000 1 28
1996-09-03 00:00:00.000 2 27
1996-09-04 00:00:00.000 3 26
1996-09-05 00:00:00.000 4 25
1996-09-06 00:00:00.000 5 24
1996-09-09 00:00:00.000 8 21
1996-09-09 00:00:00.000 8 21
1996-09-10 00:00:00.000 9 20
1996-09-11 00:00:00.000 10 19
1996-09-12 00:00:00.000 11 18
1996-09-13 00:00:00.000 12 17
1996-09-16 00:00:00.000 15 14
1996-09-17 00:00:00.000 16 13
1996-09-18 00:00:00.000 17 12
1996-09-19 00:00:00.000 18 11
1996-09-20 00:00:00.000 19 10
1996-09-20 00:00:00.000 19 10
1996-09-23 00:00:00.000 22 7
1996-09-24 00:00:00.000 23 6
1996-09-25 00:00:00.000 24 5
1996-09-26 00:00:00.000 25 4
1996-09-27 00:00:00.000 26 3
1996-09-30 00:00:00.000 29 0

Which is the range of data from the sample database Northwinds Tables Orders...The USE Statement should have brought you there.

Isn't that what you want?|||OK Ok, thanks! It is working now in Northwind... :)

Some suggested I use a Stored Procedure for doing this...

""You'll want to create a Stored Procedure in the SQL Server and then pass it's result into a DataSet to use a the Source for your DataGrid.

Try a Stored Procedure like this, Changing table and field names appropriately:
Code:
------------------------

CREATE PROCEDURE sp_BetweenDates ( @.StartDate DATETIME, @.EndDate DATETIME ) AS SELECT * FROM YOURTABLENAMEHERE WHERE [YOURDATEFIELDNAMEHERE] Between @.StartDate And @.EndDate;GO
------------------------

Then you would just pass the two dates as parameters to the Stored Procedure.""

What do you think of something like this?|||Okay, i have contrusted this query that gives me what I want...

USE billing1SQL2
GO

SELECT Hours.Employ#, Hours.Purchord, Hours.Datewrk, Hours.Hourswrk, Hours.typewrk, Hours.formwrk, Hours.class, Hours.brate, PurchaseOrder.Descr, Employee.Lastname, Employee.Firstname, Employee.[Employ#] AS Expr1, Hours.[Ticket#], PurchaseOrder.Purchord AS Expr2 FROM Hours As Hours INNER JOIN PurchaseOrder As PurchaseOrder ON Hours.Purchord = PurchaseOrder.Purchord INNER JOIN Employee As Employee ON Hours.[Employ#] = Employee.[Employ#] WHERE Hours.Datewrk between '6/15/2004' And '6/28/2004' ORDER BY Hours.Datewrk, Employee.Lastname

I just need to figure out how to use it with VB.Net the best way. Suggestions?|||Long answer:
I would start from:
System.Data.SqlClient Namespace (help is available with VB.Net)
Then I would take a class or read a book about T-SQL
Same thing for SQL server.

Short answer:
Public Sub ReadMyData(myConnString As String)
Dim mySelectQuery As String = "SELECT OrderID, Customer FROM Orders"
Dim myConnection As New SqlConnection(myConnString)
Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
myConnection.Open()
Dim myReader As SqlDataReader = myCommand.ExecuteReader()
Try
While myReader.Read()
Console.WriteLine((myReader.GetInt32(0).ToString & ", " & myReader.GetString(1)))
End While
Finally
' always call Close when done reading.
myReader.Close()
' always call Close when done reading.
myConnection.Close()
End Try
End Sub 'ReadMyData
(source: VB.Net help)

good luck!|||In your VB code, you can create your query like this

dim str1 as string

str1 = "exec sp_between_dates '" & Format(Text1.text, "mm/dd/yy") & _
"','" & Format(Text2.text, "mm/dd/yy") & "'"

Roshmi Choudhury|||Okay, thank you! soo I have this stored procedure:

CREATE PROCEDURE sp_BetweenDates (@.StartDate DATETIME, @.EndDate DATETIME)
AS
SELECT *
FROM Hours WHERE [DateWrk] Between @.StartDate And @.EndDate;
GO

..and this in VB.net...

Dim str1 As String

str1 = "exec sp_BetweenDates '" & Format(txtDate1.Text, "mm/dd/yy") & _
"','" & Format(txtDate2.Text, "mm/dd/yy") & "'"

Do I need to put my txtDates.text into a variable/string "StartDate" and "EndDate" and put that in my str call, like this... ??

str1 = "exec sp_BetweenDates '" & Format(StartDate, "mm/dd/yy")

I'm just unclear how the best way to get the actual date from my text box, to the stored procedure works...