select 'USD' as [Base_Currency],
       '1' as Basis,
       '0' as MultiCurrency,
       isnull(substring(fiscal_month, 0, 3), '') as [FiscYrEnd],
       isnull(convert(VARCHAR(10), cast(ad.filecreatedate as SMALLDATETIME), 112) - 1, '') as [Inception_Date],
       rtrim(ltrim(ad.account_short_name)) as Name,
       case
         when ad.filecreatedate is null then ''
         else convert(VARCHAR(10), cast(ad.filecreatedate as SMALLDATETIME), 112) - 1
       end as [Perf_Begin_Date],
       rtrim(ltrim(ad.bank + ad.account_number)) as Portfolio,
       '10' as Type,
       case
         when ad.udf5 like 'MIBS-MAAP%' then 'Monthly'
         when ad.investment_authority_id = '1' then 'Monthly'
         else 'Daily'
       end as [Val_Freq],
       ad.bank + ad.account_number as [External_ID],
       'NULL' as Title2,
       'NULL' as Misc1,
       'NULL' as Misc2,
       CASE ad.investment_authority_id
         WHEN '2' THEN 'Sole'
         WHEN '1' THEN 'Directed'
         WHEN '3' THEN 'Joint'
         WHEN '4' THEN 'Unknown'
       END AS inv_auth,
       isnull(perf_sched, 'NULL') as Perf_Sch,
       isnull(priority, 'NULL') as FIS_Priority_Date,
       'NULL' as [1Page_Refresh_Freq],
       isnull(ad.portfolio_manager, 'NULL') as PM,
       isnull(ad.ALT_PM, 'NULL') as [Alt_PM],
       isnull(ad.andi_model, 'NULL') as [Inv_Model],
       isnull(ad.administrator, 'NULL') as Admin,
       'System Template' as Template
from   account_detail ad
       left join #performaccounts p
         on ad.account_number = p.accountnumber
where  ad.closed_on_date is null
       and ad.DD3 is not null
       and p.accountnumber is null
       and ad.bank <> '7R'
        or p.accountname <> ad.account_short_name
           and ( ad.sub_account not in ( '00', 'AA' )
                  or ( ad.combined_account_code not in ( 'c', 'r' )
                        or ad.combined_account_code is null ) )
order  by rtrim(ltrim(ad.bank + ad.account_number)) 

isnull(convert(VARCHAR(10), cast(ad.filecreatedate as SMALLDATETIME) - 1, 112), '') as [Inception_Date],

quote:

---

Originally posted by Lamprey

I'd suggest using COALESCE over ISNULL for Ansi compliance, but that doesn't seemt to matter to most people. But, to you your problem... try:

isnull(convert(VARCHAR(10), cast(ad.filecreatedate as SMALLDATETIME) - 1, 112), '') as [Inception_Date],

---