Solved

MySQL zero date (0000-00-00) in .net C# 4.0 and Entity Framework

Posted on 2011-02-17
5
1,110 Views
Last Modified: 2012-05-11
Hi,

I wrote a a .net MVC application which is connected to a MySQL 5.09 database via EF4. I am writing some records where I would need to insert the special MySQL date of 0000-00-00 as many queries from other apps connected need this. C# does not accept it as a valid date and I can only pass DateTime.MinValue which is 0001 etc. How do I get 0000-00-00? I could make it nullable but they insist to have that special MySQL format?
0
Comment
Question by:nikomanek
  • 2
  • 2
5 Comments
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 34922776
You should be able to format it as a string and mysql will take it.
0
 

Author Comment

by:nikomanek
ID: 34922835
That probably would work but then I can't really use this anymore in my application unless I cast it everywhere? Right now I have this in my connection string (Convert Zero Datetime = True) to being able to read these dates. I just can't write them. My entity classes assume these are date values. It would be great if I could keep it that way. Alternatively I thought that I just write a trigger like:

create trigger on xyz before insert for each row beginn set new.datecolumn='0000-00-00' end
but this MySQL database complains about wrong syntax no matter how I write it. Maybe not supportet in that version.
Unfortunately this problem is a true show stopper for my app as it is use less unless it can write zero date.  
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 34922863
I personally use 1900/1/1 for fake dates. Everythif is happy with this...
What is special about 0000-00-00 in mysql?
0
 

Author Comment

by:nikomanek
ID: 34922922
There are a couple apps which where written by previous third parties which hard coded that particular zero date into their queries. We would have to change many applications if I can't write that date. The 0000-00-00 is a special MySQL zero date. Thats why they picked it (for reasons beyond me)
0
 
LVL 74

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 34940635
Why don't you create your own type which wraps a DateTime and also exposes the concept of "zero-date" (similar to a Nullable<> class). Here is an example of what I am referring to:


Data Structure
public class MyCustomDate
{
    private long _ticks;

#region Constructors

    public MyCustomDate()
    {
        this._ticks = 0;
    }

    public MyCustomDate(DateTime d)
    {
        this._ticks = d.Ticks;
    }

    public MyCustomDate(long ticks)
    {
        this._ticks = ticks;
    }

#endregion

#region Properties

    public bool IsSpecialDate
    {
        get { return (this._ticks == 0); }
    }

#endregion

#region Public Methods

    public override string ToString()
    {
        if (this._ticks == 0)
        {
            return "0000-00-00";
        }
        else
        {
            return (new DateTime(this._ticks)).ToString("yyyy-mm-dd");
        }
    }

#endregion

#region Operators

    public static implicit operator MyCustomDate(DateTime d)
    {
        return new MyCustomDate(d.Ticks);
    }

#endregion
}

Open in new window



Usage
MyCustomDate d = new MyCustomDate();

Console.WriteLine("Has value? {0}", (!d.IsSpecialDate).ToString());
Console.WriteLine(d);

d = new MyCustomDate(DateTime.Now.Ticks);

Console.WriteLine("Has value? {0}", (!d.IsSpecialDate).ToString());
Console.WriteLine(d);
Console.ReadKey();

d = DateTime.Now;

Console.WriteLine("Has value? {0}", (!d.IsSpecialDate).ToString());
Console.WriteLine(d);

Console.ReadKey();

Open in new window

0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now