get month in format dd/MM/yyyy with tipe data varchar(10)

[ade.julianto]

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

[Fenrir()]

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

[ade.julianto]

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

[george4986]

do u want it on front end or back end?

[ade.julianto]

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

[ddanbe]

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.

[george4986]

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();
           }

[Fenrir()]

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

[Lucaci Andrew]

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.

[Lucaci Andrew]

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.

[Ketsuekiame]

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

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