SQL Query Filter Start Date And End Date RSS

• • Reply
  

  afastars

  Member

  52 Points

  221 Posts

  SQL Query Filter Start Date And End Date

  Dec 09, 2012 07:54 AM|LINK

  hi all expertise

  below is my sql query that i create from Ms Sql, the query below can run without ant error

   the problem is i want to add this query below into my system, i already try it so many time but still got error, please see my code below,  need help for this problem

   

  Sql Query

  SELECT     MaklumatPelajar.Nama, MaklumatPelajar.NoIc, KesalahanDisiplin.KategoriKes, KesalahanDisiplin.SubKes, KesalahanDisiplin.TarikhKes
  FROM        KesalahanDisiplin INNER JOIN
              MaklumatPelajar ON KesalahanDisiplin.IdPelajar = MaklumatPelajar.IdPelajar
  where kesalahandisiplin.TarikhKes between '1-01-2012'  and '31-03-2012'

   

  Filter Data Query In System

  carian = "SELECT DISTINCT MaklumatPelajar.Nama, MaklumatPelajar.NoIc, KesalahanDisiplin.KategoriKes, KesalahanDisiplin.SubKes" & ",KesalahanDisiplin.AduanOleh FROM MaklumatPelajar INNER JOIN KesalahanDisiplin ON MaklumatPelajar.IdPelajar = KesalahanDisiplin.IdPelajar" & _
               " where KesalahanDisiplin.SubKes like '%" + tapis + "%' And kesalahandisiplin.TarikhKes between='" + txttarikhmula.Text + "'" + "And'" + txttarikhtamat.Text
  

  
   

  • Edited by afastars on Dec 09, 2012 07:55 AM
• • Reply
  

  oned_gk

  All-Star

  36078 Points

  7363 Posts

  Re: SQL Query Filter Start Date And End Date

  Dec 09, 2012 07:57 AM|LINK

  try

  SELECT     MaklumatPelajar.Nama, MaklumatPelajar.NoIc, KesalahanDisiplin.KategoriKes, KesalahanDisiplin.SubKes, KesalahanDisiplin.TarikhKes
  FROM        KesalahanDisiplin INNER JOIN
              MaklumatPelajar ON KesalahanDisiplin.IdPelajar = MaklumatPelajar.IdPelajar
  where kesalahandisiplin.TarikhKes between #1-01-2012#  and #31-03-2012#

  
  try using "#"

  kesalahandisiplin.TarikhKes between #" + txttarikhmula.Text + "# And #" + txttarikhtamat.Text + "#"

  Programming to simplify, dont look for hard way
  Suwandi - Non Graduate Programmer
  • Edited by oned_gk on Dec 09, 2012 08:01 AM
  • Edited by oned_gk on Dec 09, 2012 08:02 AM
• • Reply
  

  afastars

  Member

  52 Points

  221 Posts

  Re: SQL Query Filter Start Date And End Date

  Dec 09, 2012 08:05 AM|LINK

  hi oned_gk

  thanks for reply i already implement as you said but still no luck so far

  below is my code

   

  carian =

  "SELECT DISTINCT MaklumatPelajar.Nama, MaklumatPelajar.NoIc, KesalahanDisiplin.KategoriKes, KesalahanDisiplin.SubKes" & ",KesalahanDisiplin.AduanOleh FROM MaklumatPelajar INNER JOIN KesalahanDisiplin ON MaklumatPelajar.IdPelajar = KesalahanDisiplin.IdPelajar" & _" where KesalahanDisiplin.SubKes like '%" + tapis + "%' And kesalahandisiplin.TarikhKes between #" + txttarikhmula.Text + "# And #" + txttarikhtamat.Text + "#"

   

  • Edited by afastars on Dec 09, 2012 08:09 AM
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: SQL Query Filter Start Date And End Date

  Dec 10, 2012 01:25 AM|LINK

  afastars

  afastars

  Hi,

  For datetime filtering, you should add Hour, Minutes and Seconds. Just like this bold one:

  SELECT     MaklumatPelajar.Nama, MaklumatPelajar.NoIc, KesalahanDisiplin.KategoriKes, KesalahanDisiplin.SubKes, KesalahanDisiplin.TarikhKes FROM        KesalahanDisiplin INNER JOIN             MaklumatPelajar ON KesalahanDisiplin.IdPelajar = MaklumatPelajar.IdPelajar where kesalahandisiplin.TarikhKes between '1-01-2012 00:00:00'  and '31-03-2012 23:59:59'

  So you can change your statement like this:

  carian =

  "SELECT DISTINCT MaklumatPelajar.Nama, MaklumatPelajar.NoIc, KesalahanDisiplin.KategoriKes, KesalahanDisiplin.SubKes" &  ",KesalahanDisiplin.AduanOleh FROM MaklumatPelajar INNER JOIN KesalahanDisiplin ON MaklumatPelajar.IdPelajar = KesalahanDisiplin.IdPelajar" & _" where KesalahanDisiplin.SubKes like '%" + tapis + "%' And kesalahandisiplin.TarikhKes between '" + txttarikhmula.Text + " 00:00:00' And '" + txttarikhtamat.Text + " 23:59:59'"

  Or please use >= and <= instead of "between……and".

• • Reply
  

  afastars

  Member

  52 Points

  221 Posts

  Re: SQL Query Filter Start Date And End Date

  Dec 10, 2012 03:14 AM|LINK

  hi decker thanks for reply

  on my table the column type for the date is setup to nvarchar because i want the date follow my format like this 04-12-2012

  so base on your post is that i need to change the data type for the date column..?

  if i don't want to do that thing (change data type for date column) what is other way that can be done...?

• • Reply
  

  oned_gk

  All-Star

  36078 Points

  7363 Posts

  Re: SQL Query Filter Start Date And End Date

  Dec 10, 2012 04:06 AM|LINK

  store your date as datetime type. You can format whatever you want in the web page.
  Programming to simplify, dont look for hard way
  Suwandi - Non Graduate Programmer
  • Marked as answer by afastars on Dec 10, 2012 05:35 AM
• • Reply
  

  afastars

  Member

  52 Points

  221 Posts

  Re: SQL Query Filter Start Date And End Date

  Dec 10, 2012 04:22 AM|LINK

  will try to do that wait for a while

• • Reply
  

  afastars

  Member

  52 Points

  221 Posts

  Re: SQL Query Filter Start Date And End Date

  Dec 10, 2012 04:35 AM|LINK

  hi decker and oned_gk

  i already change the column data type to datetimem, on the gridview i want the date display by usng this format

  31-12-2012

  dd-MM-yyyy i don't want the time include also in my grid view,, so how it can be done

• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: SQL Query Filter Start Date And End Date

  Dec 10, 2012 04:38 AM|LINK

  afastars

  so how it can be done

  Please use Eval and bind with the formation;)

  <%#Eval("ColumnName","yyyy-MM-dd")%>

• • Reply
  

  afastars

  Member

  52 Points

  221 Posts

  Re: SQL Query Filter Start Date And End Date

  Dec 10, 2012 05:06 AM|LINK

  hi decker i put the date on the grid view when i put you code i got an error

  please help me to deterimine where the code should place

  below is my code of the grid view

  <asp:GridView ID="GridView1" runat="server"
      CellPadding="4" ForeColor="#333333" GridLines="Vertical" AutoGenerateColumns="False"
                                                      DataSourceID="SqlDataSource1" Width="946px" AllowPaging="True"
                                                      PageSize="15" style="text-align: center">
                                                      <PagerSettings PageButtonCount="5" />
                                                      <RowStyle BackColor="#EFF3FB" />
                                                      <Columns>
                                                          <asp:TemplateField HeaderText="No">
                                                           <ItemTemplate>
                                                           <%#Container.DataItemIndex + 1%>
                                                          </ItemTemplate>
                                                          </asp:TemplateField>
                                                          <asp:BoundField DataField="Nama" HeaderText="Nama" SortExpression="Nama" />
                                                          
                                                          <asp:BoundField DataField="NoIc" HeaderText="No Ic" SortExpression="NoIc" />
                                                          
                                                          <asp:BoundField DataField="KategoriKes" HeaderText="Kategori Kes"
                                                              SortExpression="KategoriKes" />
                                                              
                                                          <asp:BoundField DataField="SubKes" HeaderText="Sub Kes"
                                                              SortExpression="SubKes" />
                                                              
                                                          <asp:BoundField DataField="TarikhKes" HeaderText="TarikhKes" SortExpression="TarikhKes"
   <%#Eval("TarikhKes","yyyy-MM-dd")%>/>
                                                   
                                                          <asp:BoundField DataField="AduanOleh" HeaderText="Aduan"
                                                              SortExpression="AduanOleh" />
                                                      </Columns>
                                                      <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                                                      <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                                                      <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                                                      <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                                                      <EditRowStyle BackColor="#2461BF" />
                                                      <AlternatingRowStyle BackColor="White" />
                                                  </asp:GridView>

  please look at the bold, i put on that but got errror

  • Edited by afastars on Dec 10, 2012 05:07 AM