How to convert a string Date from database into Integer?

Question

I have a string date in database. I did like below mentioned format but it shows error like

input string was not in a correct format

But when I referred with internet this method is correct but it does not work for me. Let me know the reason?

string str1 = "select todate from Employee where EmpCode='" + code + "'";

SqlDataReader dr1 = conn.query(str1);

if (dr1.Read())
{
    string  todate1 = dr1[0].ToString();
    int todate2 =Convert.ToInt32(todate1);
}

Is the date actually stored as a string in the database to start with? If so, why? If at all possible, use a `DateTime` column in the database. Also, you should use parameterized SQL rather than including values directly in your SQL statement - see http://www.bobby-tables.com — Jon Skeet, Nov 15 '14 at 08:32
(And no, the approach you're taking is not "correct" even if you use `Convert.ToDateTime`. If the value *is* stored as a string in the database, you should use `DateTime.ParseExact`, specifying the format in which it's being recorded. But hopefully it won't come to that.) — Jon Skeet, Nov 15 '14 at 08:32
Having just seen your title, what would it even mean to convert a "string date" into an integer? What is the integer value of November 15th 2014? — Jon Skeet, Nov 15 '14 at 08:33
You need to clear which integer part of date , you need, it can be 15 , 11, 2014 for above example by Jon. — Arindam Nayak, Nov 15 '14 at 08:34
@ Jon Skeet,Why I want to convert this string date to integer is for perform a greater then operation **if(todate>fdate)**. — Bruno Blane, Nov 15 '14 at 08:35
@BrunoBlane , you can do that using - http://msdn.microsoft.com/en-us/library/system.datetime.compare(v=vs.110).aspx or - http://msdn.microsoft.com/en-us/library/system.datetime.op_greaterthan(v=vs.110).aspx — Arindam Nayak, Nov 15 '14 at 08:36
For `DateTime` comparison. Look at this: http://stackoverflow.com/questions/3059497/how-to-compare-datetime-in-c — Shaharyar, Nov 15 '14 at 08:37
@BrunoBlane: You don't need it to be an integer to perform comparisons. And we still don't know whether it's *actually* being stored in a sane way, i.e. as a `DateTime` instead of as a `VarChar`... — Jon Skeet, Nov 15 '14 at 08:37
@Jon Skeet, It stored as a string(varchar) not as a Datetime>that is the problem — Bruno Blane, Nov 15 '14 at 08:39

Jon Skeet · Accepted Answer · 2014-11-15T10:56:38.873

8

It sounds like you should be using a DateTime column in the database, at which point there's no need for integers or strings:

var today = DateTime.Today; // Or maybe use DateTime.Now
// Use parameterized SQL rather than string concatenations
string sql = "select todate from Employee where EmpCode=@EmpCode";
using (var conn = new SqlConnection(...))
{
    conn.Open();
    using (var command = new SqlCommand(sql, conn))
    {
        command.Parameters.Add("@EmpCode", SqlDbType.VarChar).Value = code;
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                DateTime date = reader.GetDateTime(0);
                if (today > date)
                {
                    // Do something
                }
            }
        }
    }
}

If your date really is being stored as a string and you can't change that (and if you can, then definitely do so), you can use DateTime.ParseExact instead:

// Other code as before
while (reader.Read())
{
    DateTime date = DateTime.ParseExact(reader.GetString(0),
                                        "yyyy-MM-dd", // Or whatever the format is
                                        CultureInfo.InvariantCulture);
    if (today > date)
    {
        // Do something
    }
}

Note that in both cases, this uses the system local time zone. You may want to consider storing all values in UTC, and performing all calculations that was as well - in which case you can use DateTime.UtcNow.

edited Nov 15 '14 at 10:56

answered Nov 15 '14 at 08:41

Jon Skeet

1,421,763
867
9,128
9,194

I tried like this **DateTime dt = (DateTime.ParseExact(todate1,"dd/MM/yyyy", CultureInfo.InvariantCulture));** no error but the **dt** value is system date value(mm/dd/yyyy) not my todate value format – Bruno Blane Nov 15 '14 at 08:58
@BrunoBlane: No, a `DateTime` value doesn't *have* a format. It's just a date and time. The result of calling `ToString()` on it will depend on your system settings. That's irrelevant as far as the comparison is concerned. – Jon Skeet Nov 15 '14 at 09:01
All my database dates are in **"dd/MM/yyyy"** format but when I convert this to Datetime the format changes to **"MM/dd/yyyy"** it affects my project. Why I saved dates in varchar format is for avoid the time with date I mean eg:"21/11/2014 :12:00:00 Am" to avoid this deafault time.Is there any other way to avoid these time format without convert this datetime into string?? – Bruno Blane Nov 15 '14 at 09:18
@Bruno: No, it doesn't convert them to a different format. The DateTime doesn't have a format at all. If it "affects your project" that just means you've got a bug elsewhere, possibly due to just calling ToString without specifying the format you want. And a DateTime is always a date and time, so your reason for storing them as varchar doesn't really make sense. – Jon Skeet Nov 15 '14 at 09:21
yes the DateTime shows our system time format because I changed my system time format to **yyyy/MM/dd" then datetime date shows date in this format even if we saved in other foramt likev **dd/MM/yyyy** – Bruno Blane Nov 15 '14 at 09:30
This is the main problem when we run our software with other systems if their system date format is different from our project date format, it shows error like "not a valid datetime format". Then we have to change their system date format to our project date foramt. Then only it works – Bruno Blane Nov 15 '14 at 09:33
@BrunoBlane: Yes, because (as I *keep* saying) the `DateTime` value doesn't store a format at all. It's just a date and time. – Jon Skeet Nov 15 '14 at 09:34
@BrunoBlane: No, you don't have to change the system format. You just need to *explicitly* specify the format whenever you convert to or from a string, which you should do as rarely as possible (hint: not for database operations!). For example: `string text = date.ToString("dd/MM/yy", CultureInfo.InvariantCulture);` Ditto for parsing - if you're expecting a specific format, then you should supply that as per my answer. But as I said, you should get away from string conversions wherever possible - keep the data as a `DateTime` as far as you can. – Jon Skeet Nov 15 '14 at 09:35
All our software operation is related with Database. Our software is a (ERP) payroll software. – Bruno Blane Nov 15 '14 at 09:38
@BrunoBlane: Then you should be storing your values in `DateTime` columns, at which point string formats are irrelevant - you pass them into the database using parameterized SQL and `DateTime` values, and you get them out as `DateTime` values... – Jon Skeet Nov 15 '14 at 09:42
anyway you make me learn something..I will follow these instructions hereafter..Nice to talk to you..Thank you :) – Bruno Blane Nov 15 '14 at 09:45
In your answer in place of **while(reader.Read())** if i use **if(dr1.Read())** how can we retrive this dr1[0] value to datetime?? – Bruno Blane Nov 15 '14 at 10:37
@BrunoBlane: In exactly the same way as with my code - I chose to use a `while` in case there was more than one result, that's all. – Jon Skeet Nov 15 '14 at 10:38
You mean like this **" DateTime date = DateTime.ParseExact(dr1.GetDateTime(0), "yyyy-MM-dd", CultureInfo.InvariantCulture);** – Bruno Blane Nov 15 '14 at 10:40
@Bruno: Sorry, that was a typo. It should be GetString. – Jon Skeet Nov 15 '14 at 10:56
I did like this ** DateTime fdate= dr1.GetDateTime(1);** – Bruno Blane Nov 15 '14 at 11:35
@Bruno: well that's unlikely to work (or may work only on some systems) if you're storing the date as a string in the database... – Jon Skeet Nov 15 '14 at 11:36
ya I changed database varchar datatype to **Date** – Bruno Blane Nov 15 '14 at 11:43

How to convert a string Date from database into Integer?

1 Answers1