try this:

select s.IDENTIFIERS,
       s.store_id,
       isnull(d.SALES_PRICE,isnull(u.SALES_PRICE,s.SALES_PRICE)) as SALES_PRICE
       from BOOK_SALES as s
       LEFT OUTER JOIN store AS BV 
                    ON bv.store_id = s.store_id 
   left outer join discount as d
                on d.IDENTIFIERS=s.IDENTIFIERS
               and d.from_date<=s.sales_date
               and d.to_date>=s.sales_date
               and d.to_date='12/12/1989'
   left outer join discount as u
                on u.IDENTIFIERS=s.IDENTIFIERS
               and u.to_date='12/12/1989'

IFNULL is use in the My-sql not in sql server follow this link null function in database

NVL is Oracle syntax

The corresponding function in SQL server is ISNULL :

AND (ISNULL(t9.ei_q18m06, t7.e_end) > '31-Jul-' | | (TO_CHAR("sysdate", 'YYYY') + least(SIGN(("sysdate" - to_date('01-Aug-' | | TO_CHAR("sysdate", 'YYYY'), 'DD-Mon-RRRR'))), 0) + - 5))

Even better, use COALESCE() which is standard ANSI and accepted by both Oracle and SQL server

AND (COALESCE(t9.ei_q18m06, t7.e_end) > '31-Jul-' | | (TO_CHAR("sysdate", 'YYYY') + least(SIGN(("sysdate" - to_date('01-Aug-' | | TO_CHAR("sysdate", 'YYYY'), 'DD-Mon-RRRR'))), 0) + - 5))

SQL NULL Functions

Add [dbo] in prefix and then execute as same :

SELECT [dbo].[Split_On_Upper_Case] ('SaiBharath')