Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2004
    Location
    STL
    Posts
    45

    Unanswered: 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!!!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hows about...
    Code:
    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
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2004
    Location
    STL
    Posts
    45
    Okay, now whats up with this
    Code:
    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.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2004
    Location
    STL
    Posts
    45
    Yes, I have those tools installed, but I really don't know how to use them to my benefit.....

  6. #6
    Join Date
    Aug 2004
    Location
    STL
    Posts
    45
    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?

  7. #7
    Join Date
    Dec 2003
    Posts
    46

    Similar Problem

    Quote Originally Posted by Brett Kaiser
    Hows about...
    Code:
    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
    Code:
    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

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Has anyone cut and pasted my code in to query analyzer and executed it?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Aug 2004
    Location
    STL
    Posts
    45
    That's what I was trying to do earlier..... I'm not a pro with the analyzer though..

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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

    Code:
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Aug 2004
    Location
    STL
    Posts
    45
    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?

  12. #12
    Join Date
    Aug 2004
    Location
    STL
    Posts
    45
    Okay, i have contrusted this query that gives me what I want...

    Code:
    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?

  13. #13
    Join Date
    Apr 2004
    Location
    LA, CA
    Posts
    125
    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!

  14. #14
    Join Date
    Feb 2002
    Location
    Assam, India
    Posts
    55

    Smile

    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

  15. #15
    Join Date
    Aug 2004
    Location
    STL
    Posts
    45
    Okay, thank you! soo I have this stored procedure:

    Code:
    CREATE PROCEDURE sp_BetweenDates (@StartDate DATETIME, @EndDate DATETIME)
    AS 
    SELECT * 
    FROM Hours WHERE [DateWrk] Between @StartDate And @EndDate;
    GO
    ..and this in VB.net...

    Code:
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •