?
Solved

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

Posted on 2011-02-17
5
Medium Priority
?
1,411 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 39

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 39

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 75

Accepted Solution

by:
käµfm³d   👽 earned 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

719 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