Link to home
Start Free TrialLog in
Avatar of RIAS
RIASFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Find data in a column which is not in a date format

Hello,

Can anyone suggest a query to  find all the data in a table where a column data is not in date format.

Cheers
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

SELECT *
FROM TableName
WHERE ISDATE(ColumnName)=0

Open in new window

Avatar of RIAS

ASKER

Thanks Vitor,
Any suggestions on how to move that data to another field in the same table?
Any suggestions on how to move that data to another field in the same table?
Assuming that TargetColumnName is a varchar data type and you want to store only non date values:
UPDATE TableName
SET TargetColumnName = SourceColumnName
WHERE ISDATE(SourceColumnName)=0

Open in new window

Avatar of RIAS

ASKER

But, Vitor that field2 where we are going to move the data already has data in there.  is it Possible to move the non dates field data  to field2.
Example;

Field1     Field2
2Days     Data Already Present.

After the query :

UPDATE TableName
SET TargetColumnName = SourceColumnName
WHERE ISDATE(SourceColumnName)=0



Field1     Field2
                 Data Already Present. 2 Days
Yes, it possible as it will overwrite the old data. If you want to append instead of overwrite them use this version:
UPDATE TableName
SET Field2 = Field2 + ' ' + Field1
WHERE ISDATE(Field1)=0

Open in new window

Avatar of RIAS

ASKER

Will try and get back .Thanks
Avatar of RIAS

ASKER

Thanks Vitor,

Will try and give you the points.
Besides, I will request you to please help in the question if possible.

https://www.experts-exchange.com/questions/28995982/Replace-Dates-in-query.html
Avatar of RIAS

ASKER

Vitor,


UPDATE CORRESPONDENCE1
SET Comments = Comments + ' ' + DepartureDate
WHERE ISDATE(DepartureDate)=0

is there any way i can remove the data from DepartureDate field which is non date
is there any way i can remove the data from DepartureDate  field which is non date
Sure. Do that after the previous UPDATE command run successfully and also take a backup before deleting the data in case of something goes wrong.
I am just not sure if you want to delete all rows:
DELETE CORRESPONDENCE1
WHERE ISDATE(DepartureDate)=0

Open in new window

or if you want to clear only the information from the column. If the latest one then you can do everything in the UPDATE (again, run a backup first):
UPDATE CORRESPONDENCE1
SET Comments = Comments + ' ' + DepartureDate,
   DepartureDate = NULL
 WHERE ISDATE(DepartureDate)=0

Open in new window

Avatar of RIAS

ASKER

Vitor,
Why a date format :
19/04/2016 coming up as non date?
Avatar of RIAS

ASKER

Also the query :

UPDATE CORRESPONDENCE
SET Comments = Comments + '; Departure Date: ' + DepartureDate
WHERE ISDATE(DepartureDate)=0

 failed where Comments was null.
Hi Rias,

Well IsDate will  not work in this case. Try below -

Select ISDATE('04- December 20') - This will give you 1.

** - Please do not use IsDate(). You have to write your custom function to check whether it is date or not.

Hope it helps!
Avatar of RIAS

ASKER

Any suggestions?
failed where Comments was null.
For this the solution is simple:
UPDATE CORRESPONDENCE
 SET Comments = ISNULL(Comments,'') + '; Departure Date: ' + DepartureDate
 WHERE ISDATE(DepartureDate)=0
SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
19/04/2016 coming up as non date?
That's depends on the server settings. In your case it might see this date as MM/DD/YYYY then complaining because 19 is not a month.
How many of these cases do you have?
Avatar of RIAS

ASKER

4 thousand
Pls try my suggestion.
I don't have experience with the TRY_CONVERT presented by Pawan but you can try to use it and see if solves the issue:
UPDATE CORRESPONDENCE
SET Comments = ISNULL(Comments,'') + '; Departure Date: ' + DepartureDate
WHERE TRY_CONVERT(DepartureDate) IS NULL 

Open in new window

Avatar of RIAS

ASKER

Now all the non dates departure dates are with value :

1900-01-01
How can i get rid of this value
Avatar of RIAS

ASKER

The alter query was:

 ALTER TABLE [dbo].[CORRESPONDENCE]
              ALTER COLUMN [DepartureDate] Date NULL
How did you come to the ALTER COLUMN step?
Can you explain all steps that you did?
Avatar of RIAS

ASKER

SELECT *
FROM CORRESPONDENCE
WHERE TRY_CONVERT(DATE,   DepartureDate) IS NULL

 UPDATE CORRESPONDENCE  SET Comments = ISNULL(Comments,'') + '; Departure Date: ' + DepartureDate
 WHERE TRY_CONVERT(DATE, DepartureDate) IS NULL

UPDATE CORRESPONDENCE
SET
   DepartureDate = ''
 WHERE TRY_CONVERT(DATE, DepartureDate) IS NULL


 ALTER TABLE [dbo].[CORRESPONDENCE]
              ALTER COLUMN [DepartureDate] Date NULL

And then this terrible thingy :


Now all the non dates departure dates are with value :

1900-01-01
How can i get rid of this value
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RIAS

ASKER

Life saver!
Glad to help. 👍
Avatar of RIAS

ASKER

Cheers!
Not Best solution for Life Saver. :)
Avatar of RIAS

ASKER

oh Life Saver was for both!!!