As long as I have been working with the M2M Database I had always wondered one thing. Why on earth does M2M use 1/1/1900 dates? It’s fairly obvious that it was an artificial date when one wasn’t applicable, but why? Why not just allow your date fields to be null and be done with it?
For example, when I run the following query to return the Last Payment Date (fdlpaydate):
select fdlpaydate, fcustno, fcompany from slcdpm
Why Should I Care About 1/1/1900?
For me, the reason was simple. My executives hate the way it clutters reports. They’d much rather display a blank field if there isn’t a valid date. In the past, I would use a “Print When” option in Visual FoxPro or something analogous in Crystal Reports. In other words, I would hide the bogus date in the presentation layer. As I migrate to SQL Server Reporting Services, I wanted a more universal method to suppress these dates so they would be suppressed no matter which presentation medium I chose.
The 1900 Date is Actually a Null?
No, not exactly. I investigated this because I needed to know how to suppress it. When one looks at the create statement of any date field in the M2M database, the same constraint is used:
[fdlpaydate] datetime NOT NULL
“Not Null” is the constraint. It turns out that the database returns the 1900 date because the value is actually ‘’ and not null.
How do we Fight the 1900 Scourge?
Well, first before implementing any code from this site, please read my disclaimer. What I’ve chosen to do is to create a User Defined Function (UDF).
CREATE FUNCTION ExtractDate(@DirtyDate DATETIME) RETURNS VARCHAR(10) AS BEGIN DECLARE @CleanDate VARCHAR(10) SELECT @CleanDate = CASE WHEN @DirtyDate = '' THEN '' ELSE CONVERT(VARCHAR(10), @DirtyDate, 101) END RETURN @CleanDate END
You’ll notice that I check the date for the empty string ‘’ and then “filter” out the meaningless dates. The convert statement returns a string in the typical Month/Day/Year format.
How do I Use the User Defined Function?
The only trick to using a UDF is remembering to reference the owner, in this case DBO, when calling it. Therefore, you would use the user defined function like this:
SELECT DBO.ExtractDate(fdlpaydate), fcustno, fcompany FROM slcdpm
Feel free to use this (after you’ve read and understood the disclaimer). However, you have made a change to your M2M database, and you should document doing so. It’s unlikely that such a change would affect your ability to migrate upward, but you can never be too careful. Document it, and be prepared to delete it if you have problems.
Any questions or suggestions?