Solved

mysql test a date friend for value  greater then zeroes

Posted on 2014-10-08
7
254 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
[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
  • 4
  • 2
7 Comments
 
LVL 66

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
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

 
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 66

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…
Viewers will learn how the fundamental information of how to create a table.

728 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