Solved

sql to convert to date IF entry is in date format

Posted on 2016-09-18
4
61 Views
Last Modified: 2016-09-18
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
0
Comment
Question by:amucinobluedot
  • 2
  • 2
4 Comments
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
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
 

Author Comment

by:amucinobluedot
Comment Utility
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
 
LVL 32

Accepted Solution

by:
Stefan Hoffmann earned 500 total points
Comment Utility
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
 

Author Comment

by:amucinobluedot
Comment Utility
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now