Solved

mysql test a date friend for value  greater then zeroes

Posted on 2014-10-08
7
253 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 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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 to return specific rows and columns, with various degrees of sorting and limits in place.

726 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