sql to convert to date IF entry is in date format

I am copying information from one table to another. There is one field which is an nvarchar and should in fact contain dates in the following format:  mm/dd/yyyy  or mm/dd/yy
I am going to copy that date into another field which is actually a datetime field.
I am using the following sql

CONVERT(VARCHAR(255), CONVERT(DATE, LastUSEntry), 101) as ArrivalDateD,

the only problem is that there are entries in "LastUSEntry" that are NOT dates or they are not in the correct format. Some say "NA" or "1/1/02".  I Only want to copy over dates that can be converted into the datetime format, such as those in mm/dd/yy or mm/dd/yyyy

How can I do this ?  Otherwise right now the sql above would fail because of those entries that aren't dates. Also there are null fields, I don't think those will be a problem but just in case I mention it.

I am using SQL 1008R2
LVL 1
AleksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
I guess you mean SQL Server 2008 R2? Then the only thing you can try is using ISDATE():

DECLARE @Sample TABLE ( Payload VARCHAR(255) );

INSERT INTO @Sample
VALUES	( '20160101' ),
		( '20163131' );

SELECT	CAST(S.Payload AS DATE)
FROM	@Sample S
WHERE	ISDATE(S.Payload) = 1;

Open in new window


Caveat: In complex queries this may not work if an predicate pushdown happens.
0
AleksAuthor Commented:
Ok. Translating that to my sql would it look like this ?

CONVERT(VARCHAR(255), CONVERT(ISDATE(DATE, LastUSEntry)), 101) as ArrivalDateD, 

Open in new window


Is that the correct syntax ?  if its not a date will it simply set ArrivalDateD as NULL ?
0
ste5anSenior DeveloperCommented:
DECLARE @Sample TABLE ( Payload VARCHAR(255) );

INSERT INTO @Sample
VALUES	( '20160101' ),
		( '20163131' ),
		( '12/31/2016' ),
		( '07/06/16' ) ;

SELECT	CAST(S.Payload AS DATE)
FROM	@Sample S
WHERE	ISDATE(S.Payload) = 1;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
Lets make it simpler. If it is not a date simply make it NULL. I tried but I still get an error. I am trying with this code:

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

Open in new window

Seems some entries escape the update and they are not dates so the script fails.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.