Solved

sql to convert to date IF entry is in date format

Posted on 2016-09-18
4
107 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
[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
  • 2
4 Comments
 
LVL 34

Expert Comment

by:ste5an
ID: 41803767
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
ID: 41803769
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 34

Accepted Solution

by:
ste5an earned 500 total points
ID: 41803793
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
ID: 41803855
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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

717 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