compare datetime with string

Question

0.00/5 (No votes)

See more:

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):

C#

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

Xonfused

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Shahin Khorshidnia · Accepted Answer · 2012-03-19T12:29:00

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"

SQL

SELECT * FROM TableExample WHERE [Date] > '20101010'

Posted 19-Mar-12 12:29pm

Shahin Khorshidnia

Updated 19-Mar-12 12:32pm

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?

PopeDarren · Accepted Answer · 2012-03-19T12:28:00

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. :)

Posted 19-Mar-12 12:28pm

PopeDarren

Updated 19-Mar-12 12:45pm

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!