• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1789
  • Last Modified:

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

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
nikomanek
Asked:
nikomanek
  • 2
  • 2
1 Solution
 
Aaron TomoskySD-WAN SimplifiedCommented:
You should be able to format it as a string and mysql will take it.
0
 
nikomanekAuthor Commented:
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
 
Aaron TomoskySD-WAN SimplifiedCommented:
I personally use 1900/1/1 for fake dates. Everythif is happy with this...
What is special about 0000-00-00 in mysql?
0
 
nikomanekAuthor Commented:
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
 
käµfm³d 👽Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now