hy guys.. i have a problem how to search data by month in format dd/MM/yyyy and i use tipe data varchar(10) exm: 23/01/2014

Recommended Answers

Click Here try this. if your date is varchar you will need to convert it Click Here

Jump to Post

Do you have a column with date strings always of the form xx/xx/xxxx in the front end?

In that case, extract the month using the SubString method of the String class and test with that in your search.

Jump to Post

Like george asked is this on the SQL server side or in your application?

Jump to Post

All 10 Replies

Click Here try this. if your date is varchar you will need to convert it Click Here

Thx for replay.. but i don;t want to convert. and i don't use tipe data date.. but i use tipe data varchar..

do u want it on front end or back end?

example i want search data where month 07 in my column table 01/07/2014

Do you have a column with date strings always of the form xx/xx/xxxx in the front end?

In that case, extract the month using the SubString method of the String class and test with that in your search.

use LINQ

//// sourceDt=ur dt to be filtered
/////monthVal=value of month 
/////at_date=date column
var filterCheck = from val in sourceDt.AsEnumerable()where Convert.ToDateTime(val.Field<string>("at_date")).month = monthVal
                 orderby Convert.ToDateTime(val.Field<string>("at_date")) ascending select val;
     DataView viewRslt = filterCheck.AsDataView();
        if (viewRslt.Count > 0)
           {
               ///////////RsltDt =search result
              DataTable RsltDt = viewRslt.ToTable();
           }

Like george asked is this on the SQL server side or in your application?

example i want search data where month 07 in my column table 01/07/2014

Looks like you need some query for the database:

//MySQL syntax:
SELECT * FROM table_name WHERE DATE_FORMAT(STR_TO_DATE(column_name, '%d/%m/%y'), '%m') = 'your_month_number'
-- assuming that in your table 'table_name' you have a column 'column_name'
-- which is a varchar (10) and in the format of dd/mm/yyyy (e.g 23/01/2014),
-- and 'your_month_number' is your required month to search by (e.g. 01 - January).

that is, converting your varchar field to a date, and than extracting the month part, and comparing to your desired month.

To do this in C#, assuming you have a DataTable with all your information, and you want to filter by a certain month, you can do like this (this may get messy):

//dt - DataTable with your information
string month = "02";
var rows = dt.AsEnumerable()           //get an enumerable containing your rows
    .Where(x =>                        //take each row as variable 'x'
    DateTime.ParseExact(               //convert it to a DateTime
        x.Field<string>("column_name"),//your field from the Data_Table
        "dd/MM/yyyy",                  //the format of your date from the Data_Table
        null)           
    .Month                             //get the month from the newly create date
    ==                                 //check for equality
    DateTime.ParseExact(               //convert it to a DateTime
        month,                         //your month name
        "MM",                          //the format of your month
        null)
    .Month);                           //your month, to be compared with

that is, get all the rows from my DataTable which have in 'column_name' field (which is a varchar) a certain month. You need to convert your string date from your DataTable to a DateTime using the DateTime.ParseExact method, and then you only compare the months, to see if they match.

select * from YOURTABLE where YOURDATECOLUMN like '%/07/____'

I'd suggest including the year too though...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.