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

Conversion failed when converting the varchar value '1900-01-01' to data type int.

I am getting error,

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '1900-01-01' to data type int.

This is happening when @CMD is compared to b.birthdate which holds values char(10) and are like 1969-09-02.

Why is a conversion even taken place?  Here is the script.

      --Variable Declaration
      declare @seq int
      declare @attr varchar(3)
      declare @bitmask varchar(40)
      declare @CM varchar(30) -- CM column name
      declare @CMD varchar(30) -- CM Empty String or 0 value or default date
      declare @CM2 varchar(30) -- CM2 column name
      declare @CM2D varchar(30) -- CM2 Empty String or 0 value or default date
      declare @sql varchar (4000)

set @seq = 0
set @attr = '-19'
set @bitmask = '524288'
set @CM = 'b.birthdate'
set @CMD = '1900-01-01'      
set @CM2 =       'a.birthdate'
set @CM2D = '1/1/1900'

      --Insert
                  set @sql = '
                  insert into CMvCM2.dbo.DEMO_Analysis_Results
                  select a.attr, a.cnt, b.NullCM, bb.NullCM_CM2ZorE, c.NullCM2, cc.NullCM2_CMZorE, d.BothNull, e.BothNotNull, ee.BothNotNullCMZorE, eee.BothNotNullCM2ZorE
                  from      (select ' + @attr + '  Attr, count(*) CNT
                              from CMvCM2_CompareDEMO
                              where score1 & cast(' + @bitmask + ' as bigint) = ' + @bitmask + ') a
                  join      (select ' + @attr + '  Attr, count(*) NullCM
                              from CMvCM2_CompareDEMO z
                                    join CMvCM2.dbo.cm2_Demographics_20070825 a
                                     on a.ccn = z.ccn and a.acct = z.acct
                                    join CMvCM2.dbo.cm_Demographics_20070825  b
                                     on b.ccn = z.ccn and b.acct = z.acct
                              where score1 & cast(' + @bitmask + ' as bigint) = ' + @bitmask + '
                              and ' + @CM2 + ' is not null and ' + @CM2 + ' != ' + @CM2D + ' and ' + @CM + ' is null) b
                  on a.attr = b.attr
                  join      (select ' + @attr + '  Attr, count(*) NullCM_CM2ZorE
                              from CMvCM2_CompareDEMO z
                                    join CMvCM2.dbo.cm2_Demographics_20070825 a
                                     on a.ccn = z.ccn and a.acct = z.acct
                                    join CMvCM2.dbo.cm_Demographics_20070825  b
                                     on b.ccn = z.ccn and b.acct = z.acct
                              where score1 & cast(' + @bitmask + ' as bigint) = ' + @bitmask + '
                              and ' + @CM2 + ' is not null and ' + @CM2 + ' = ' + @CM2D + ' and ' + @CM + ' is null) bb
                  on a.attr = bb.attr
                  join      (select ' + @attr + '  Attr, count(*) NullCM2
                              from CMvCM2_CompareDEMO z
                                    join CMvCM2.dbo.cm2_Demographics_20070825 a
                                     on a.ccn = z.ccn and a.acct = z.acct
                                    join CMvCM2.dbo.cm_Demographics_20070825  b
                                     on b.ccn = z.ccn and b.acct = z.acct
                              where score1 & cast(' + @bitmask + ' as bigint) = ' + @bitmask + '
                              and ' + @CM2 + ' is null and ' + @CM + ' is not null and ' + @CM + ' != ' + @CMD + ') c
                  on a.attr = c.attr
                  join      (select ' + @attr + '  Attr, count(*) NullCM2_CMZorE
                              from CMvCM2_CompareDEMO z
                                    join CMvCM2.dbo.cm2_Demographics_20070825 a
                                     on a.ccn = z.ccn and a.acct = z.acct
                                    join CMvCM2.dbo.cm_Demographics_20070825  b
                                     on b.ccn = z.ccn and b.acct = z.acct
                              where score1 & cast(' + @bitmask + ' as bigint) = ' + @bitmask + '
                              and ' + @CM2 + ' is null and ' + @CM + ' is not null and ' + @CM + ' = ' + @CMD + ') cc
                  on a.attr = cc.attr
                  join      (select ' + @attr + '  Attr, count(*) BothNull
                              from CMvCM2_CompareDEMO z
                                    join CMvCM2.dbo.cm2_Demographics_20070825 a
                                     on a.ccn = z.ccn and a.acct = z.acct
                                    join CMvCM2.dbo.cm_Demographics_20070825  b
                                     on b.ccn = z.ccn and b.acct = z.acct
                              where score1 & cast(' + @bitmask + ' as bigint) = ' + @bitmask + '
                              and ' + @CM2 + ' is null and ' + @CM + ' is null) d
                  on a.attr = d.attr
                  join      (select ' + @attr + '  Attr, count(*)  BothNotNull
                              from CMvCM2_CompareDEMO z
                                    join CMvCM2.dbo.cm2_Demographics_20070825 a
                                     on a.ccn = z.ccn and a.acct = z.acct
                                    join CMvCM2.dbo.cm_Demographics_20070825  b
                                     on b.ccn = z.ccn and b.acct = z.acct
                              where score1 & cast(' + @bitmask + ' as bigint) = ' + @bitmask + '
                              and ' + @CM2 + ' is not null and ' + @CM + ' is not null and ' + @CM + ' != ' + @CMD + ' and ' + @CM2 + ' != ' + @CM2D + ') e
                  on a.attr = e.attr
                  join      (select ' + @attr + '  Attr, count(*)  BothNotNullCMZorE
                              from CMvCM2_CompareDEMO z
                                    join CMvCM2.dbo.cm2_Demographics_20070825 a
                                     on a.ccn = z.ccn and a.acct = z.acct
                                    join CMvCM2.dbo.cm_Demographics_20070825  b
                                     on b.ccn = z.ccn and b.acct = z.acct
                              where score1 & cast(' + @bitmask + ' as bigint) = ' + @bitmask + '
                              and ' + @CM2 + ' is not null and ' + @CM + ' is not null and ' + @CM + ' = ' + @CMD + ') ee
                  on a.attr = ee.attr
                  join      (select ' + @attr + '  Attr, count(*)  BothNotNullCM2ZorE
                              from CMvCM2_CompareDEMO z
                                    join CMvCM2.dbo.cm2_Demographics_20070825 a
                                     on a.ccn = z.ccn and a.acct = z.acct
                                    join CMvCM2.dbo.cm_Demographics_20070825  b
                                     on b.ccn = z.ccn and b.acct = z.acct
                              where score1 & cast(' + @bitmask + ' as bigint) = ' + @bitmask + '
                              and ' + @CM2 + ' is not null and ' + @CM + ' is not null and ' + @CM2 + ' = ' + @CM2D + ') eee
                  on a.attr = eee.attr'
                  exec (@sql)
0
donnatronious
Asked:
donnatronious
  • 3
  • 2
1 Solution
 
YveauCommented:
Can you highlight the line on which the error occurs for us ?
0
 
donnatroniousAuthor Commented:
This variable, @CMD = '1900-01-01',   is compared against, @CM = b.birthdate for the first time in the script here,

and ' + @CM + ' != ' + @CMD + ') c

Its a little less than halfway through the insert statement, in the 3rd join.  So whats really happening is this,

and b.birthdate != '1900-01-01'.

b.birthdate is char(10) and contains values like this '1969-09-02'.

Does this help
0
 
YveauCommented:
Just before the
    exec(@sql)

run a
    select @sql

... it will show you the actual SQL statement you created and it will show you this section of code:

where   score1 & cast(524288 as bigint) = 524288
and     a.birthdate is null
and     b.birthdate is not null
and     b.birthdate != 1900-01-01

... the last clause should have extra quotes around the date. As you compare it with 1900-01-01 ... equals 1898 ! And that is the integer it is trying to convert to ... I think !
So it should say this:

where   score1 & cast(524288 as bigint) = 524288
and     a.birthdate is null
and     b.birthdate is not null
and     b.birthdate != '1900-01-01'

change the @sql to:
    where score1 & cast(' + @bitmask + ' as bigint) = ' + @bitmask + '
    and ' + @CM2 + ' is null and ' + @CM + ' is not null and ' + @CM + ' != ''' + @CMD + ''') c

so three quotes in stead of one. Again, the output of the sql statement should give some clear view on this.

Oh, another thing. When you are on SQL 2005 (not clear from the question, but it is off topic ... so ... ) you should use the varchar(max) type for the @sql parameter, just to be sure you do not cross the 4000 characters ... that would give some nasty run time problems. And they might appear only a year from now ...

Hope this helps ...
0
 
donnatroniousAuthor Commented:
Thats the problem.  Thanks a log!!!!!!!
0
 
YveauCommented:
Glad I could be of any help and thanks for the grade !
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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