Solved

mysql test a date friend for value  greater then zeroes

Posted on 2014-10-08
7
243 Views
Last Modified: 2014-10-08
new to mysql know tsql
i see where we store dates with a 0000-00-00
how do i test for a   date  that does not have zeroes
0
Comment
Question by:john M
  • 4
  • 2
7 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Give us an example of what you're trying to pull off here.

'Does not have zeros' means something like 6/9/2014?
0
 
LVL 15

Accepted Solution

by:
gplana earned 500 total points
Comment Utility
A date with all zeros means that the date entered has an invalid format.

I think you can compare directly:

SELECT *
FROM your_table
WHERE your_date='0000-00-00';

Open in new window


it this doesn't work you can do something like this:

SELECT 
FROM your_table
WHERE DAYOFMONTH(your_date)=0 AND MONTH(your_date)=0 AND YEAR(your_date)=0;

Open in new window


Hope it helps. Regards.
0
 
LVL 3

Author Comment

by:john M
Comment Utility
yes i mean '0000-00-00 in the date field.
There should be a function you can call to exclude the records.
I found if you use the field like in datediff of add a day it will exclude all '0000-00-00'
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 15

Expert Comment

by:gplana
Comment Utility
I think you can exclude them just adding  this to your WHERE clause:

AND your_date <> '0000-00-00'

Open in new window


or by adding this on your WHERE clause:
AND YEAR(your_date)<>0 

Open in new window


hope this helps.
0
 
LVL 15

Expert Comment

by:gplana
Comment Utility
where of course "your_date" means the name of the field that could have the wrong date.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>yes i mean '0000-00-00 in the date field.
I'm guessing that these values are stored in a character field, as 0000-00-00 is not in valid date format.
Running the below code in SSMS will throw an error:
CREATE TABLE #dates (dt date) 
INSERT INTO #dates (dt) 
VALUES('0000-00-00') 

Open in new window

So, assuming varchar, you can filter these rows out by using (as posted above)
WHERE your_date <> '0000-00-00'

Open in new window

, or in a single column by using
SELECT CASE your_date
   WHEN '0000-00-00'  THEN 'add something here to replace this value
   ELSE your_date
END 

Open in new window

0
 
LVL 15

Expert Comment

by:gplana
Comment Utility
JimHorn, mysql allows to store this "special" date value of 0000-00-00, it musn't be a varchar.

Your solution:
WHERE your_date <> '0000-00-00'
is exactly the same I have said.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

762 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

9 Experts available now in Live!

Get 1:1 Help Now