Member Avatar for 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

  • 6 Contributors
  • 10 Replies
  • 645 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by Ketsuekiame

Dani AI

Generated

Short answer: because your dates are fixed-format strings (dd/MM/yyyy) the quickest no-conversion approach is to pull the month substring (characters 4–5) and compare it. That is what was suggesting for front-end code, and it’s the same idea behind ’s LIKE trick — easy to implement but brittle and not index-friendly. The more robust long‑term approach (what , and hinted at) is to store real DATE values or add a persisted/generated month column so queries are fast and safe.

Example filters (do not rely on these for correctness unless the column is validated first):

-- SQL Server: simple filter on the stored text
SELECT * FROM YourTable
WHERE LEN(RTRIM(DateText)) = 10
  AND SUBSTRING(DateText, 4, 2) = '07';
// app-side quick test (zero-based indexing)
var s = row["DateText"].ToString().Trim();
if (s.Length == 10 && int.TryParse(s.Substring(3, 2), out int m) && m == 7)
{
    // match
}

If performance or correctness matters, add a persisted/generated month column and index it:

-- SQL Server: persisted computed column + index
ALTER TABLE YourTable
  ADD MonthInt AS (CAST(SUBSTRING(DateText, 4, 2) AS INT)) PERSISTED;
CREATE INDEX IX_YourTable_MonthInt ON YourTable(MonthInt);
-- MySQL: stored generated column + index
ALTER TABLE YourTable
  ADD COLUMN month_int TINYINT GENERATED ALWAYS AS (CAST(SUBSTRING(DateText,4,2) AS UNSIGNED)) STORED;
CREATE INDEX idx_month_int ON YourTable(month_int);

Do a data audit before relying on substring logic. Find malformed rows (examples for SQL Server and MySQL shown below) and fix or remove them. Longer term, migrate the column to a proper DATE: add a DATE column, backfill with the DB’s conversion routine, validate counts, add constraints, then switch application code. This keeps queries accurate across locales and lets the engine use indexes and date functions reliably.

Recommended Answers

All 10 Replies

Member Avatar for Fenrir()

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

Edited by Fenrir()
Member Avatar for 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..

Member Avatar for george4986

do u want it on front end or back end?

Member Avatar for ade.julianto

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

Member Avatar for 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.

Member Avatar for 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();
           }
Edited by george4986 because: correction
Member Avatar for Fenrir()

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

Member Avatar for 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.

Edited by Lucaci Andrew
Member Avatar for 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.

Edited by Lucaci Andrew
Member Avatar for Ketsuekiame

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 developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.