Solved

Copy only dates

Posted on 2016-09-18
3
108 Views
Last Modified: 2016-09-18
I am trying to make NULL fields that are not dates, date should only be either:

mm/dd/yyyy  OR mm/dd/yy

Anything that is not that format should be made NULL

Here is my current sql

UPDATE  dbo.Dependents SET DepLastUSEntry = NULL 
WHERE ISDATE(DepLastUSEntry)= 0

Open in new window


It is not making some of the fields NULL not sure why.  Perhaps it is allowing some 'dates' that are not in the format above, is there a way to check that it IS in the format as listed above ?
0
Comment
Question by:amucinobluedot
[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
  • 2
3 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41804131
Here you go

--

UPDATE  dbo.Dependents SET DepLastUSEntry = NULL 
WHERE ISDATE(FORMAT(DepLastUSEntry,'mm/dd/yyyy'))= 0

--

Open in new window


Pls let me know if you face any issues
0
 
LVL 29

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41804134
If above dont work then you write 2 statements

--

UPDATE  dbo.Dependents SET DepLastUSEntry = NULL 
WHERE ISDATE(FORMAT(DepLastUSEntry,'mm/dd/yyyy'))= 0


UPDATE  dbo.Dependents SET DepLastUSEntry = NULL 
WHERE ISDATE(FORMAT(DepLastUSEntry,'mm/dd/yy'))= 0

--

Open in new window

0
 

Author Closing Comment

by:amucinobluedot
ID: 41804146
Thanks!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Does your audience prefer people in photos or no people? How can you best highlight what you’re selling? What are your competitors doing, and what can you do that is different and unique from them?  Continue reading to learn how to make your images …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

724 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