NULLIF(RTRIM ) in query

Have you tested the single parts? Especially does the SELECT statement work?

SELECT IET.[In1] ,
        '' ,
        GETDATE() ,
        '' ,
        IET.[Name of Trt:] ,
        NULLIF(RTRIM(IET.[FROM]), '') ,
        NULLIF(RTRIM(IET.[TO]), '') ,
        IET.[Trion]
FROM   TABLE2 IET
WHERE  [InvemGUID] NOT IN (   SELECT [InvemGUID]
                                FROM   TABLE1 );

Select allOpen in new window

When it works, then you should consider using clean code. In T-SQL, this means to specify the column list in the INSERT clause. Relying on column order can explain your problem. Maybe you're simply trying to insert values in the wrong position (column).

FROM and TO are of date datatype? You cannot RTRIM on a date!

You can only successfully rtrim()  character based columns, but it shouldnt error, the result of rtrim() is a varchar or nvarchar and so, convert to a character based representation.

And for a date, doing a RTRIM(getdate()) will return something like  "Sep 28 2018  4:36AM"

and then, the entire  NULLIF(RTRIM(<column>),'') should also match the datatype of the recipient column.

so unless the receiving column in Table1 is also date / datetime (or can intrinsically convert) then you may encounter a problem....

Still, it *should* work ie :

create table #Table1(id int identity, [FROM] date, [TO] DATE)

create table #Table2(id int identity, [FROM] datetime, [TO] DATE)

insert #Table2 values (getdate(),getdate())
insert #Table2 values (NULL,NULL)
insert #Table2 values (getdate()-10,getdate()-10)

insert #Table1
select NULLIF(RTRIM([FROM]),''),NULLIF(RTRIM([TO]),'')
from #Table2

select * from #table1

/*
id          FROM       TO
----------- ---------- ----------
1           2018-09-28 2018-09-28
2           NULL       NULL
3           2018-09-18 2018-09-18

(3 rows affected)

*/

Select allOpen in new window

So, it works...

I am guessing now, but think that Table2.[TO] and/or Table2.[FROM] are NOT date based columns and there could be unexpected characters....

Even still, the error I would expect would be something more like "Conversion failed when converting date and/or time from character string"

There are things you can do instead of NULLIF(RTRIM(<column>),'') like checking ISDATE() and wrapping in a case statement to cast an alternate value.

So, where to from here ?

1) The exact error message
2) Datatypes of [TO] and [FROM] for both tables.
3) Is Table2 imported from somewhere else ? e.g. have seen Excel imports the string value of 'NULL'

I am quite sure we can help fix, but need more information....

I am getting error;
Date cannot be converted to string

wondering if this error is from SQL Server or from your programming language, Java, etc?

Its in sql server Ssms

checked the data type of fields in your Table1 before run the Insert script?

your inserted values must have the same data type as the targeted fields.

@RIAS,

Any feedback on the information I asked of you ?

Mark,
Apologies for the delay.


1) The exact error message :"Conversion failed when converting date and/or time from character string"
2) Datatypes of [TO] and [FROM] for both tables.Varchar in TABLE2 AND Date in Table1
3) Is Table2 imported from somewhere else ? e.g. have seen Excel imports the string value of 'NULL'
Table2 is imported from Sun System.


This is in continuation of this question:

https://www.experts-exchange.com/questions/29105554/Null-in-date-field.html


Hope that answers your questions.
Let me know.
Can't thank you enough

OK, Thanks for getting back....

So, what happened to the strategy in the other thread ?

I see you have tagged this thread with SQL 2008  and try_convert() requires 2012. Is that the difference ?

Still, would not really be changing strategy.... Just a bit of syntax changing for 2008.

So instead of  (remembering to change STYLE CODE from 103 to whatever represents the incoming string):

SELECT 
           CASE WHEN LEN(LTRIM(RTRIM(ISNULL(IET.[From], '')))) > 1 then TRY_CONVERT(DATETIME, IET.[From], 103) else NULL end
         , CASE WHEN LEN(LTRIM(RTRIM(ISNULL(IET.[To], '')))) > 1 then TRY_CONVERT(DATETIME, IET.[To], 103) else NULL end

FROM [TABLE2_EE] IET

Select allOpen in new window

Then a slightly more verbose version would be along the lines of using isdate(). But, as an example, lets first expose the data that could be causing issues:

SET DATEFORMAT DMY   -- SET DATEFORMAT MDY -- depending on your system

select * from
(SELECT  IET.[From], 
        CASE WHEN LEN(LTRIM(RTRIM(ISNULL(IET.[From], '')))) > 6 and isdate(IET.[From]) = 1 then IET.[From] + ' IS A DATE' else 'NO' end as From_Status,
        IET.[To],
        CASE WHEN LEN(LTRIM(RTRIM(ISNULL(IET.[To], '')))) > 6 and isdate(IET.[To]) = 1 then IET.[To] + ' IS A DATE' else 'NO' end as To_Status

FROM Table2 IET) src
where From_Status = 'NO'
or TO_Status = 'NO'

Select allOpen in new window

And we should adjust the test for LEN() to be an accurate indication of the actual lengths in use. So, if you could post back a couple of examples, it would help.

Now a couple of other questions for you as well....

1) Is you system set up as DMY or MDY  (ie Day Month Year, or, Month Day Year)
2) What delimiters are used in your varchar dates - are they "/" or "-" or nothing, or something else ?
3) A couple of examples of varchar dates being used ?


In the above code, we would be replacing the string status messages with

.... then convert(datetime, IET.[From],101) else null end

again, depends on the STYLE code (using 101 this time which is mm/dd/yyyy) see : https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#date-and-time-styles

But we can discuss that after the test, and responses to the questions above...

And sorry about all the questions, just wanting to get to the bottom of it all....

Thanks Mark, spot on .. will answer all the questions when I am at desk today .. thanks alot.