Solved

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

Posted on 2011-02-17
5
1,153 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 75

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Allow space in this pattern 2 48
ASP.NET 5 Templates 2 66
Close form "before" open 3 25
PHP & MySQL - Rounding Results from a Select Query 3 9
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
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…

920 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

15 Experts available now in Live!

Get 1:1 Help Now