?
Solved

sql to convert to date IF entry is in date format

Posted on 2016-09-18
4
Medium Priority
?
120 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 35

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 35

Accepted Solution

by:
ste5an earned 2000 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

Linux Academy Android App Now Supports Chromecast

We have some fantastic news for our Android fans. We’re so excited to announce that the Linux Academy Android app is now available with Chromecast support. That’s right – simply download the latest update of the Linux Academy App and start casting your favorite course videos!

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

762 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