Solved

SQL syntax to delelte data is not a date format

Posted on 2016-09-04
7
81 Views
Last Modified: 2016-09-05
I am importing data from a third party software
One of the fields they have is an nvarchar(200) but holds dates in format :  mm/dd/yyyy

Most of the entries in that field are in fact dates, but some aren't. I need to delete any entries in that field which are NOT a 'date' or in date format 'mm/dd/yyyy'

The table name is:  Dependents
The field name is:  DepLastUSEntry

Also some dates are in :  07/27/03

Can they be converted to :  07/27/2003  ?
And also:  8/25/2003  into 08/25/2003  
Maybe:  8/13.2002  into 08/13/2002

Anything different that cannot be converted into a mm/dd/yyyy should be deleted, and if the conversions above cannot be done then remove any date that is not mm/dd/yyyy




Thanks!
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
  • 3
  • 3
7 Comments
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41783867
Which SQL Server version are you using (SELECT @@Version)?

Starting wioth 2012 you may use:

SET LANGUAGE ENGLISH -- importatnt to process dates in format mm/dd/yyyy, might be optional if your server setting is in english/british/us format anyway.
DELETE FROM yourtable WHERE TRY_CONVERT(date, DepLastUSEntry) IS NULL

Open in new window


Just try some dates (especially with day>12) on try_convert to see whether it results in a date for correct nvarchar strings, so you don't delete everything.

Bye, Olaf.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41783891
>I need to delete any entries in that field which are NOT a 'date' or in date format 'mm/dd/yyyy'
DELETE means remove the entire row from the table.
UPDATE means change values in the row, say changing this column to an empty string or NULL '', and keep the row.

Tell us which you wish to do here.
0
 

Author Comment

by:amucinobluedot
ID: 41783895
Sorry. I meant update and make it null
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41783898
Well, then you would UPDATE instead of delete, but you can't update an nvarchar column with a date, you can only set those NULL, which aren't a date in string form.

SET LANGUAGE ENGLISH -- important to process dates in format mm/dd/yyyy, might be optional if your server setting is in english/british/us format anyway.
UPDATE yourtable SET DepLastUSEntry = NULL 
WHERE TRY_CONVERT(date, DepLastUSEntry) IS NULL

Open in new window


Again, test before you do, with the wrong settings (eg TRY_CONVERT converting dd/mm/yyyy instead of mm/dd/yyyy, international formats differ a lot also in regard of the dash separator) you may lose part of the dates you rather wanted to keep.

If your overall goal is to alter the table to have a date or datetime column:

Set Language English;
Create Table #Test (DepLastUSEntry nvarchar(200));
Insert Into #Test Values (N'12/31/2010'), (N'31/12/2015');
Select * From #Test;

Update #Test set DepLastUSEntry = NULL WHERE TRY_CONVERT(date,DepLastUSEntry) IS NULL;

Alter Table #Test Alter Column DepLastUSEntry Date NULL;
Select * From #Test;
Drop Table #Test;

Open in new window


You would execute lines 1, 6, and 8 only, obviously.

Bye, Olaf.
0
 

Author Comment

by:amucinobluedot
ID: 41784584
I tried this, but I am using SQL 2008R2

SET LANGUAGE ENGLISH -- important to process dates in format mm/dd/yyyy, might be optional if your server setting is in english/british/us format anyway.
UPDATE yourtable SET DepLastUSEntry = NULL 
WHERE TRY_CONVERT(date, DepLastUSEntry) IS NULL

Open in new window


Error

Msg 195, Level 15, State 10, Line 5
'TRY_CONVERT' is not a recognized built-in function name.

Open in new window

0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 500 total points
ID: 41784730
That's why I asked for the version. Then try it with ISDATE():

First see, if these dates are correct:
SELECT DepLastUSEntry FROM yourtable WHERE ISDATE(DepLastUSEntry)=1

Open in new window


If so, this update will change all other invalid ones to NULL:
UPDATE yourtable SET DepLastUSEntry = NULL 
WHERE ISDATE(DepLastUSEntry)=0

Open in new window

Bye, Olaf.
0
 

Author Closing Comment

by:amucinobluedot
ID: 41784953
This worked!
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL: Only Wanting One Record 8 54
Remove () 9 31
invoke-sqlcmd help 5 22
T-SQL: need to reset a declared variable 4 23
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…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

734 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