RIAS
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
Can anyone suggest a query to find all the data in a table where a column data is not in date format.
Cheers
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?
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
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
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
ASKER
Will try and get back .Thanks
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
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
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
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 dateSure. 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
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
ASKER
Vitor,
Why a date format :
19/04/2016 coming up as non date?
Why a date format :
19/04/2016 coming up as non date?
ASKER
Also the query :
UPDATE CORRESPONDENCE
SET Comments = Comments + '; Departure Date: ' + DepartureDate
WHERE ISDATE(DepartureDate)=0
failed where Comments was null.
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!
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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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
ASKER
Now all the non dates departure dates are with value :
1900-01-01
How can i get rid of this value
1900-01-01
How can i get rid of this value
ASKER
The alter query was:
ALTER TABLE [dbo].[CORRESPONDENCE]
ALTER COLUMN [DepartureDate] Date NULL
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?
Can you explain all steps that you did?
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Life saver!
Glad to help. 👍
ASKER
Cheers!
Not Best solution for Life Saver. :)
ASKER
oh Life Saver was for both!!!
Open in new window