Aleks
asked on
SQL syntax to delelte data is not a date format
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!
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!
>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.
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.
ASKER
Sorry. I meant update and make it null
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.
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:
You would execute lines 1, 6, and 8 only, obviously.
Bye, Olaf.
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
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;
You would execute lines 1, 6, and 8 only, obviously.
Bye, Olaf.
ASKER
I tried this, but I am using SQL 2008R2
Error
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
Error
Msg 195, Level 15, State 10, Line 5
'TRY_CONVERT' is not a recognized built-in function name.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This worked!
Starting wioth 2012 you may use:
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.