?
Solved

sorting records bwtween 2 dates?

Posted on 2003-03-31
15
Medium Priority
?
225 Views
Last Modified: 2010-04-17
hi,
    can someone tell how to write a sql query for sorting records between 2 dates in vb....
           
0
Comment
Question by:contact9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 30

Expert Comment

by:Mayank S
ID: 8238086
Why not use the ORDER BY clause?
0
 
LVL 1

Expert Comment

by:FrankTheRat
ID: 8238100
I don't know about the VB bit the the SQL should look something like:

SELECT * FROM TableName WHERE (DateFieldName > '01-Jun-1999' AND DateFieldName < '01-Dec-1999').

Hope this helps.

FrankTheRat

0
 

Author Comment

by:contact9
ID: 8238122
hey franktherat the dates r not fixed and they r entered by user,,,,,,the dates r taken from user and then the records are sorted.......
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 53

Expert Comment

by:Dhaest
ID: 8238318
sql = "SELECT * FROM TableName " _
& "WHERE (DateFieldName > '" & format(InputDate1,"dd-mm-yyyy") & "' AND DateFieldName < '" format(InputDate2,"dd-mm-yyyy") & "') order by DateFieldName"

0
 
LVL 1

Expert Comment

by:FrankTheRat
ID: 8240452
My application takes the date values from a couple of DateTime controls and also allows sorting by any table field. The application presents all available fields in a drop-down combobox and the

For example (this is not syntactically accurate, but you get the idea):
SELECT * FROM TableName WHERE (DateFieldName > DateCTRL1.Date AND DateFieldName < DateCTRL2.Date) ORDERBY FieldComboCTRL1.Text).

Others may have more comprehensive advice if you let us know what language you're using and what sort of application architecture you're using.

FrankTheRat
0
 

Author Comment

by:contact9
ID: 8261195
To franktherat,
              see i am using vb as front end and sql as backend,...i have taken 2 text boxes and a datagrid,,,,
the user should enter the start date in frist text box and end date a tthe secondtext box,,the result must be displayed in the datagrid.
     
0
 
LVL 1

Expert Comment

by:FrankTheRat
ID: 8263561
Sorry, I don't know VB.

Delphi has non-visual controls that connect to databases (e.g. TQuery) so that the developer can build a query string from your visual controls (eg Text controls) and submit an SQL query to the database.

To make life easier, there are even 'data aware' controls that connect to database fields.

If VB has a DateTime control, this would be better than a plain text control as you won't have to parse the input yourself.

In my example above, DateCTRL1.Date provides access to a DateTime formated value from a standard Delphi control of class TDateTimePicker, which can just be inserted in the SQL query string.

Sorry I can't offer more VB related help.

Good luck.

FrankTheRat
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 8267011
You can use the DateDiff () function in VB to compare 2 dates:

Dim d1 As Date
Dim d2 As Date
Dim diff As Long
..
.. ' read values
..
..
diff = DateDiff ( "d", d1, d2 ) ' "d" stands for 'date' - you can also find the difference for months, years, hours, etc

If diff = 0 Then
  ' same date
Else
  If diff > 0 Then
    ' d2 comes after d1
  Else
    ' d1 comes after d2
  End If
End If

You can use the CDate () function to convert a string to a date as:

Dim d As Date
Dim str As String

str = "May 25, 1982"
d = CDate (str)


Hope that helps!

Mayank.
0
 

Author Comment

by:contact9
ID: 8274674
hello dahest
          i am using sorting out this result in data grid,,,,,
but when i fire this query on a command button,,,,,,nothing happens,,,,,,,any solution,,,,,
                              ujjwal
0
 
LVL 8

Expert Comment

by:spongie
ID: 8283199
Are trying to only get only the records that fall between two dates? if so try this...

Dim startDate as date
Dim endDate as date
Dim strSQL as string

startDate = Format(textbox1.text,"Short Date")
endDate = Format(textbox2.text, "Short Date")

strSQL = "Select * from [tablename] where [datefield] > #" & _
   DateAdd("d",-1,startDate) & "# And [datefield] < #" & _
    DateAdd("d",1,endDate) & "# Order by [datefield] ASC"

...run strSQL. This would work even when the date format in your database includes hours, minutes, and seconds. Sometimes, you don't get the desired results because of these hours and minutes.  Dates that are equal to the end and start date will also be returned in the query.  Hope this helps...
0
 

Author Comment

by:contact9
ID: 8283439
hi spongie,
           see i am viewing this records in a datagrid,,,,

i have taken a datagrid two text boxes to accept the 2 dates..
this is the code for it,.......

 'On Error Resume Next:
Dim sortrs As New Recordset
Dim strsql As String
 strsql = "SELECT certno,dtofrec from pharma where dtofrec>" & CDate(Text1.Text) & " and dtofrec< " & CDate(Text2.Text) & "order by dtofrec"
 Set DataGrid1.DataSource = sortrs
 sortrs.Open strsql, con, adOpenStatic
DataGrid1.Width = 5000

'DataGrid1.Columns(2).Width = 4000
    With DataGrid1
'
         Set .DataSource = sortrs
                DataGrid1.Columns(0).Width = 4000
               DataGrid1.Columns(1).Width = 4000
         
        .ClearFields
     .ReBind
 End With
sortrs.close

,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
bur when i click on command button only trhe headings of cloumn are see not the sorted records.,,,,,,,,,,,
                        c u help me,,,,,,in this regard..

                                                   ujjwal




0
 
LVL 8

Expert Comment

by:spongie
ID: 8292394

Try this...



Set sortrs = New Recordset
Dim strsql As String
strsql = "SELECT certno,dtofrec from pharma where dtofrec > #" & CDate(Text1.Text) & "# and dtofrec < #" & CDate(Text2.Text) & "# order by dtofrec"
sortrs.Open strsql, con, adOpenStatic, adLockReadOnly
DataGrid1.Width = 5000


   With DataGrid1

        Set .DataSource = sortrs
               DataGrid1.Columns(0).Width = 4000
              DataGrid1.Columns(1).Width = 4000
       
       .ClearFields
       .ReBind
   
End With


...see the difference? no 'sortrs.close' (closing the recordset would also close the datasource) and the '#' signs. This query wouldn't include the ones that are equal to the beginning and end dates though. try including the codes i posted previously.

hope this solves your problem....
0
 
LVL 8

Expert Comment

by:spongie
ID: 8292397

Try this...



Set sortrs = New Recordset
Dim strsql As String
strsql = "SELECT certno,dtofrec from pharma where dtofrec > #" & CDate(Text1.Text) & "# and dtofrec < #" & CDate(Text2.Text) & "# order by dtofrec"
sortrs.Open strsql, con, adOpenStatic, adLockReadOnly
DataGrid1.Width = 5000


   With DataGrid1

        Set .DataSource = sortrs
               DataGrid1.Columns(0).Width = 4000
              DataGrid1.Columns(1).Width = 4000
       
       .ClearFields
       .ReBind
   
End With


...see the difference? no 'sortrs.close' (closing the recordset would also close the datasource) and the '#' signs. This query wouldn't include the ones that are equal to the beginning and end dates though. try including the codes i posted previously.

hope this solves your problem....
0
 

Author Comment

by:contact9
ID: 8299291
thanks i got it
0
 
LVL 8

Accepted Solution

by:
spongie earned 150 total points
ID: 8300912
Good. Glad to hear that. Just click the answer as accepted though. Thanks.
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Six Sigma Control Plans
Suggested Courses
Course of the Month8 days, 8 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question