title: How to avoid default date 1900-01-01 in SQL Server 2008 R2 during join statement
body: <pre><code>SELECT IsNull(cmg.[Advertiser],
      con.[Advertiser]) as [Advertiser] ,
      IsNull(cmg.[AdvertiserID],
      con.[AdvertiserID]) as [AdvertiserID] ,
      con.[FloodlightConfiguration] ,
      IsNull(cmg.[Campaign],
      con.[Campaign]) as [Campaign] ,
      IsNull(cmg.[CampaignID],
      con.[CampaignID]) as [CampaignID] ,
      IsNull(cmg.[Placement],
      con.[Placement]) as [Placement] ,
      IsNull(cmg.[PlacementID],con.[PlacementID]) as [PlacementID] ,
      IsNull(cmg.[Creative],con.[Creative]) as [Creative] ,
      IsNull(cmg.[CreativeID],con.[CreativeID]) as [CreativeID] ,
      IsNull(cmg.[AD],con.[AD]) as [AD] ,
      IsNull(cmg.[ADID],
      con.[ADID]) as [ADID] ,
      IsNull(cmg.[Country],
      con.[Country]) as [Country] ,
      con.[Activity] ,
      con.[ActivityID] ,
      IsNull(cmg.[DeliveryDate],0) as [ConversionDate] ,
      IsNull(con.[TotalConversions],0) as [TotalConversions] ,
      IsNull(con.[TotalRevenue],0) as [TotalRevenue] ,
      IsNull([ClickthroughConversions],0) as [ClickthroughConversions] ,
      IsNull([ClickthroughRevenue],0) as [ClickthroughRevenue] ,
      IsNull([ViewthroughConversions],0) as [ViewthroughConversions] ,
      IsNull([ViewthroughRevenue],0) as [ViewthroughRevenue] ,
      case when con.[ActivityID] is not null then 0 else cmg.Impressions end as Impressions,
      case when con.[ActivityID] IS NOT Null then 0 else cmg.Clicks end AS Clicks,
      case when con.[ActivityID] IS NOT NULL then 0 else cmg.MediaCost end as MediaCost,
      case when con.[ActivityID] IS NOT NULL then 0 else cmg.ActivityPerClick end as ActivityPerClick,
      case when con.[ActivityID] IS NOT NULL then 0 else cmg.ActivityPerThousandImpressions end as ActivityPerThousandImpressions,
      case when con.[ActivityID] IS NOT NULL then 0 else cmg.CostPerActivity end as CostPerActivity,
      case when con.[ActivityID] IS NOT NULL then 0 else cmg.CostPerClick end as CostPerClick,
      case when con.[ActivityID] IS NOT NULL then 0 else cmg.CostPerRevenue end as CostPerRevenue,
      case when con.[ActivityID] IS NOT NULL then 0 else cmg.EffectiveCPM end as EffectiveCPM ,
      case when con.[ActivityID] IS NOT NULL then 0 else cmg.RevenuePerClick end as RevenuePerClick,
      case when con.[ActivityID] IS NOT NULL then 0 else cmg.RevenuePerThousandImpressions end as RevenuePerThousandImpressions 
  FROM [DM_886_MarriottSocialData].[dbo].[DFA_BSE_DIS_MD_1] cmg
      FULL OUTER JOIN [DM_886_MarriottSocialData].[dbo].[DFA_BSE_DIS_CON_1] con
          ON cmg.PlacementID = con.PlacementID 
              AND cmg.DeliveryDate = con.ConversionDate 
              AND cmg.CampaignID = con.CampaignID 
              AND cmg.Country = con.Country 
              AND cmg.ADID = con.ADID 
              AND cmg.CreativeID = con.CreativeID 
              AND cmg.AdvertiserID = con.AdvertiserID 
  WHERE cmg.DeliveryDate &gt;= '2014-01-01' 
      AND cmg.DeliveryDate &lt;= '2014-03-16'
  </code></pre>
  
  <p>This is the code I am using currently to evaluate aggregate table but i am getting 1900-01-01 default date in some records. however, i want those all records in final table. The reason is because of 1900-01-01 some columns are effecting with 5%-10% of fluctuation in their respective values.</p>
  
  <p>Thanks</p>