• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 149
  • Last Modified:

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
0
Aleks
Asked:
Aleks
  • 2
  • 2
1 Solution
 
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now