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
Solved

mysql test a date friend for value  greater then zeroes

Posted on 2014-10-08
7
250 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
ID: 40368366
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
ID: 40368383
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
ID: 40368394
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 15

Expert Comment

by:gplana
ID: 40368461
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
ID: 40368464
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
ID: 40368485
>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
ID: 40368508
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

856 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