Click here to Skip to main content
12,945,933 members (38,064 online)
Rate this:
 
Please Sign up or sign in to vote.
Hi, what i want to do is to populate a datagridview from a database, but with comparing a string from the database with a date..
Here is what i wanna do:

in the database for example i have a column with the value "19.03.2012" (as a string type, i tried to make the datetime type but no success), and i want to select the values that are greater then for example "10.10.2010" and fill the datagrid with the specified value

here is the code where i call to populate the datagrid (i am aware why this code wont work, it is just so you know what i want to do):
private void PopulateDataGrid()
        {
            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM TableExample WHERE Date > '10.10.2010'", conn);
            SqlCommandBuilder cb = new SqlCommandBuilder(da);
 
            DataTable table = new DataTable();
            table.Locale = System.Globalization.CultureInfo.InvariantCulture;
            da.Fill(table);
            TableExampleBindingSource.DataSource = table;
 
            dataGridView1.ReadOnly = true;
            dataGridView1.DataSource = TableExampleBindingSource;
        }


So how can i use greater or less then, when i want to compare dates?

Thanks in forward...
Posted 19-Mar-12 12:16pm
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Hello

Bad way!

If you are using SQL Server, use DateTime instead of nvarchar.

By the way:
If you want and insist to use string type for date, then change your format. For example:
"20120123" instead of "01.23.2012"

SELECT * FROM TableExample WHERE [Date] > '20101010'
  Permalink  
v4
Comments
nltd 22-Dec-15 4:51am
   
Hi! thank you so much. But my problem is not at SQL. I got date into SQL and i compared them then true. When both string datetime get into SQL, datetime.now have same format ( dd/MM/yyyy). But i set format datetime in my computer... it was wrong :(.
ex: i have
string date_now = "2015/12/22";
string date_end ="22/12/2015";
how way can i compare them?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

This is quick and dirty, and I don't recommend it. hah! I'm also making a lot of assumptions here... Try this out for an example:

SELECT CONVERT(DATETIME, '19.03.2010', 103)

This will convert your European date/string to the american format. So then you can do this:

SELECT * FROM TableExample WHERE Date > CONVERT(DATETIME, '19.03.2010', 103)

If the problem is that the date could be in any number of formats, then you might think about running a cleaner once you have the data in the dataTable and then doing a table.Select.

The other option would be to clean the data in SQL and change the interface to only accept certain dates/formats. If you give users the chance to enter crap, they will. :)
  Permalink  
v2
Comments
nltd 22-Dec-15 4:59am
   
thank PopeDarren! Now i only want to compare 2 string, which have format datetime different. :)
And I was a beginner programmers so still have a lot be need to learn. Can you give me a solution ? plz.
PopeDarren 24-Aug-16 18:44pm
   
I haven't been on this site in a while... sorry for the late response. You really should start a new question and not reply to another question in order to get a good answer.
Well... again, I'd be making a bunch of assumptions...
Below is a test console project I threw together to test formatting dates in C#. From your other replies on this message, it looks like you're in C# and not SQL. Please note that this is NOT a complete solution. This could cause dates to end up in the wrong format and you won't know until looking at the data later, which is, obviously, not good. For example: 10/11/12 will return a date for all three formats found below (Oct 11 2012, 10th November 2012, 2010 November 12). If at all possible, you'll need much more business logic in order to get this one right... and you'll still probably get junk values. However, IF they're just in the two formats you talked about above (yyyy/MM/dd and dd/MM/yyyy) that makes it much easier. You can take the MM/dd/yyyy format out of the code below and just use the other two. You can also replace the date format arrays with just the single formats if your years are always four digits.

static void Main(string[] args)
{
string one = "12/22/2015";
string two = "22/12/2015";
string three = "2015/12/22";

DateTime dateOne, dateTwo, dateThree;

dateOne = ConvertDate(one);
dateTwo = ConvertDate(two);
dateThree = ConvertDate(three);

if (dateOne == dateTwo)
Console.WriteLine("Dates one and two are equal.");

if (dateOne == dateThree)
Console.WriteLine("Dates one and three are equal.");

if (dateTwo == dateThree)
Console.WriteLine("Dates two and three are equal.");
}

public static DateTime ConvertDate(string inVal)
{
DateTime dateValue;
if (DateTime.TryParseExact(inVal, new string[] { "MM/dd/yyyy", "MM/dd/yy" }, null, DateTimeStyles.None, out dateValue))
{
Console.WriteLine("American Date: {0} to {1}",
inVal, dateValue.ToString());
return dateValue;
}
else
{
if (DateTime.TryParseExact(inVal, new string[] { "dd/MM/yyyy", "dd/MM/yy" }, null, DateTimeStyles.None, out dateValue))
{
Console.WriteLine("Great Britain Date: {0} to {1}",
inVal, dateValue.ToString());
return dateValue;
}
else
{
if (DateTime.TryParseExact(inVal, new string[] { "yyyy/MM/dd", "yy/MM/dd" }, null, DateTimeStyles.None, out dateValue))
{
Console.WriteLine("Japan Date: {0} to {1}",
inVal, dateValue.ToString());
return dateValue;
}
else
{
Console.WriteLine("fail");
throw new Exception("Date does not fit known format.");
}
}
}
}

HTH!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 4,899
CHill60 3,180
Maciej Los 2,453
Jochen Arndt 1,910
ppolymorphe 1,785


Advertise | Privacy | Mobile
Web02 | 2.8.170518.1 | Last Updated 19 Mar 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100