Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams INSERT INTO OtherData1 (OtherDataID, EmployeeID, OtherDate, OType, OSubject, StatementNo, StatementDate, Info, OtherAs, FolderSerial) SELECT OtherDataID, EmployeeID, CONVERT(DATE, OtherDate, 103), OType, OSubject, StatementNo, CONVERT(DATE, StatementDate), Info, CASE OtherAs WHEN 'f' THEN 1 WHEN 's' THEN 2 WHEN 't' THEN 3 WHEN 'f' THEN 4 WHEN 'p' THEN 5 WHEN 'o' THEN 6 ELSE NULL END , FolderSerial FROM OtherData

I when I execute it I get this error :

Msg 241, Level 16, State 1, Line 5
Conversion failed when converting date and/or time from character string.
the values for StatementDate, OtherDate are in this format "31/1/1994" as string before convert
What is the problem?
EDIT:
if I added this line of code :
CASE WHEN ISDATE(OtherDate) = 1 THEN 
  CONVERT(DATE, OtherDate, 103)
ELSE NULL END
I tried it and it's not working, I need some thing like it to check if vaild do the convert if not insert NULL value and I get the same error message
                @OMG Ponies: I know it's wrong but it was from very long time and we did it for custom App and now we updated every thing and we need to copy data to new table with new types
– Saleh
                Feb 26, 2011 at 0:40
                select CONVERT(date, '31/1/1994', 103) works for me regardless of dateformat settings. I guess you must have some invalid dates in there.
– Martin Smith
                Feb 26, 2011 at 0:45
ISDATE won't work since you cannot even specify a date format as you can with CONVERT.  The following is a VERY elaborate test to reformat valid dates into YYYYMMDD for which ISDATE works predictably.
with otherdata(StatementDate) as (
select convert(varchar(10),'1/10/2011') union all
select '28/2/2911' union all
select '8/12/2011' union all
select '13/13/2011' union all
select '13/12/2011' union all
select '12/13/2011' union all
select '2/29/2011' union all
select '29/2/2011' union all
select '29/2/2012' union all
select '2011-02-01' union all
select '1/1/11' union all
select '1/1/99' union all
select '' union all
select null)
-- THE QUERY YOU NEED is below this line. The above virtually sets up a table
-- without having to physically create it
select
    statementdate,
    YYYYMMDDToTest,
    ISDate(YYYYMMDDToTest)
from otherdata
cross apply
       select TheYear = case
       when not statementdate like '%[0-9]/%[0-9]/%[0-9][0-9]' then null
       when convert(int,replace(statementdate,'/','')) != replace(statementdate,'/','') then null
       when statementdate like '%[0-9]/%[0-9]/[0-9][0-9]' then
           case when RIGHT(statementdate,2) >=50
           then '19'+RIGHT(statementdate,2)
           else '20'+RIGHT(statementdate,2)
       when statementdate like '%[0-9]/%[0-9]/[1-9][0-9][0-9][0-9]' then
           RIGHT(statementdate,4)
cross apply
       select YYYYMMDDToTest = case
       when TheYear is not null then
           TheYear
           + -- month
           right(100+SUBSTRING(statementdate, charindex('/',statementdate) +1,
           charindex('/',statementdate,charindex('/',statementdate)+1)-
           charindex('/',statementdate)-1),2)
           + -- day
           right(100+LEFT(statementdate, charindex('/', StatementDate) -1),2)
WHERE ISDate(YYYYMMDDToTest) = 0
Comment out the last line WHERE ISDate(YYYYMMDDToTest) = 0 to see what it does with each date.
You can turn this into a function that replaces the ISDATE - but for the SPECIFIC format [d]d/[m]m/yyyy.
create function dbo.superIs103Date(@any varchar(50))
returns bit as begin
declare @theyear varchar(10)
set @TheYear = case
       when not @any like '%[0-9]/%[0-9]/%[0-9][0-9]' then null
       when convert(int,replace(@any,'/','')) != replace(@any,'/','') then null
       when @any like '%[0-9]/%[0-9]/[0-9][0-9]' then
           case when RIGHT(@any,2) >=50
           then '19'+RIGHT(@any,2)
           else '20'+RIGHT(@any,2)
       when @any like '%[0-9]/%[0-9]/[1-9][0-9][0-9][0-9]' then
           RIGHT(@any,4)
declare @YYYYMMDDToTest varchar(50)
set @YYYYMMDDToTest = case
       when @TheYear is not null then
           @TheYear
           + -- month
           right(100+SUBSTRING(@any, charindex('/',@any) +1,
           charindex('/',@any,charindex('/',@any)+1)-
           charindex('/',@any)-1),2)
           + -- day
           right(100+LEFT(@any, charindex('/', @any) -1),2)
return ISDate(@YYYYMMDDToTest)
-- example usage
select dbo.superIs103Date('33/1/1700')
-- returns 0
                Can you explain to me a little more, how I can apply this query because I was tyring to do it and I have no idea how to merge it with my query
– Saleh
                Feb 26, 2011 at 12:00
This won't get all possible invalid dates (e.g. 30 February) but might help you find ones that need fixing.
SELECT StatementDate 
 FROM OtherData
 WHERE StatementDate NOT LIKE '[0-3][0-9]/[0-1][0-9]/[1-2][0-9][0-9][0-9]'
  AND  StatementDate NOT LIKE '[1-9]/[0-1][0-9]/[1-2][0-9][0-9][0-9]'
  AND  StatementDate NOT LIKE '[0-3][0-9]/[1-9]/[1-2][0-9][0-9][0-9]'
  AND  StatementDate NOT LIKE '[1-9]/[1-9]/[1-2][0-9][0-9][0-9]'
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.