Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: Dynamically Passing Date Value from SQL Server to Oracle Server

    I have this script and get the errors below:


    -- declare variables:
    declare @FSUserID as nvarchar(30)
    declare @FSPW as nvarchar(30)
    declare @AffiliateName as nvarchar(50)
    declare @effDate as datetime
    declare @sql as nvarchar(2000)


    -- set variable values:
    set @FSUserID = '''MYUSERNAME'''
    set @FSPW = '''MYPASSWORD'''
    set @AffiliateName = '''CLIENT COMPANY NAME%'''
    set @effDate = '31-DEC-4000'


    -- place statement into variable
    set @sql = 'select *
    from openrowset(' + '''MSDAORA''' + '
    ,' + '''SERVERNAME'';' + @fsUserID + ';' + @fsPW + '
    ,' + '''select u.hp_purch_ik as "FS Purchaser ID"
    , upper(u.hp_purch_nm) as "FS Purchaser Name"
    , u.grp_size_cd
    , p.cntct_party_ik as "Broker No"
    , upper(p.frst_nm) as "FS First Name"
    , upper(p.lst_nm) as "FS Last Name"
    , c.role_nm
    , p.aflt_nm
    , p.eff_end_dt as "Party End Date"
    , c.eff_end_dt as "Period End Date"

    from vcntc_cntct_party p
    join vcntp_cntct_prd c
    on c.fk_cntc_party_ik = p.cntct_party_ik
    join vhppr_hp_purch u
    on u.hp_purch_ik = c.fk_cntr_purch_ik

    join (
    select fk_cntc_party_ik
    , hp_purch_ik
    , max(seq_nb) as max_seq_nb
    , max(eff_end_dt) as max_eff_end_dt
    from vcntp_cntct_prd vcntp
    join vhppr_hp_purch vhppr
    on vcntp.fk_cntr_purch_ik = vhppr.hp_purch_ik
    where eff_end_dt = ''' + convert(varchar(23),@effDate, 105) + '''
    group by fk_cntc_party_ik
    , hp_purch_ik
    ) vcntp2
    on vcntp2.hp_purch_ik = u.hp_purch_ik
    and vcntp2.fk_cntc_party_ik = c.fk_cntc_party_ik
    and vcntp2.max_eff_end_dt = c.eff_end_dt
    and vcntp2.max_seq_nb = c.seq_nb

    where p.eff_end_dt = ''' + convert(varchar(23),@effDate) + '''
    and c.eff_end_dt = ''' + convert(varchar(23),@effDate) + '''

    order by "FS Purchaser Name"
    , "FS Last Name"
    , "FS Purchaser ID"'')'


    PRINT @sql

    execute sp_executesql @sql

    --------------------------------------

    Results:

    select *
    from openrowset('MSDAORA'
    ,'HERAPROD.WORLD';'Y879204';'permanente1'
    ,'select u.hp_purch_ik as "FS Purchaser ID"
    , upper(u.hp_purch_nm) as "FS Purchaser Name"
    , u.grp_size_cd
    , p.cntct_party_ik as "Broker No"
    , upper(p.frst_nm) as "FS First Name"
    , upper(p.lst_nm) as "FS Last Name"
    , c.role_nm
    , p.aflt_nm
    , p.eff_end_dt as "Party End Date"
    , c.eff_end_dt as "Period End Date"

    from vcntc_cntct_party p
    join vcntp_cntct_prd c
    on c.fk_cntc_party_ik = p.cntct_party_ik
    join vhppr_hp_purch u
    on u.hp_purch_ik = c.fk_cntr_purch_ik

    join (
    select fk_cntc_party_ik
    , hp_purch_ik
    , max(seq_nb) as max_seq_nb
    , max(eff_end_dt) as max_eff_end_dt
    from vcntp_cntct_prd vcntp
    join vhppr_hp_purch vhppr
    on vcntp.fk_cntr_purch_ik = vhppr.hp_purch_ik
    where eff_end_dt = '31-12-4000' -- < This is where I am getting this error
    group by fk_cntc_party_ik
    , hp_purch_ik
    ) vcntp2
    on vcntp2.hp_purch_ik = u.hp_purch_ik
    and vcntp2.fk_cntc_party_ik = c.fk_cntc_party_ik
    and vcntp2.max_eff_end_dt = c.eff_end_dt
    and vcntp2.max_seq_nb = c.seq_nb

    where p.eff_end_dt = 'Dec 31 4000 12:00AM'
    and c.eff_end_dt = 'Dec 31 4000 12:00AM'

    order by "FS Purchaser Name"
    , "FS Last Name"
    , "FS Purchaser ID"')
    Server: Msg 170, Level 15, State 1, Line 29
    Line 29: Incorrect syntax near '31'.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    March 12, 4000?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Posts
    193
    That's the default value for a "current" record Blindman. I know, I never bothered to ask, I just query the database servers like a quiet, little Data Analyst is supposed to.

    Also, I do have permissions and connectivity and tested such on simpler queries. I do get results. It is just with this date issue that I am having trouble. I have read by googling all day that I must use either convert or cast in the dynamic sql string.

    ddave

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by dolfandave
    That's the default value for a "current" record Blindman. I know, I never bothered to ask
    No need to ask. I can tell you. It was some sophomoric dba-wannabe who read some nonsense on the internet about not storing NULL values in a database.
    Try formatting the date in YYYY-MM-DD format instead:
    "4000-12-31'"
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Posts
    193
    Now I get this error:

    Server: Msg 207, Level 16, State 3, Line 13
    Invalid column name ''4000-12-31''.

    I am fiddling w/ different combinations here but none of them seem to work. Bear in mind I am passing this to an Oracle server.

    ddave



    ----------------------------
    -- declare variables:
    declare @FSUserID as nvarchar(30)
    declare @FSPW as nvarchar(30)
    declare @AffiliateName as nvarchar(50)
    declare @effDate as datetime
    declare @sql as nvarchar(2000)


    -- set variable values:
    set @FSUserID = '''MYUSERNAME'''
    set @FSPW = '''MYPASSWORD'''
    set @AffiliateName = '''CLIENT COMPANY NAME%'''
    set @effDate = "'4000-12-31'"


    -- place statement into variable
    set @sql = 'select *
    from openrowset(' + '''MSDAORA''' + '
    ,' + '''SERVERNAME'';' + @fsUserID + ';' + @fsPW + '
    ,' + '''select u.hp_purch_ik as "FS Purchaser ID"
    , upper(u.hp_purch_nm) as "FS Purchaser Name"
    , u.grp_size_cd
    , p.cntct_party_ik as "Broker No"
    , upper(p.frst_nm) as "FS First Name"
    , upper(p.lst_nm) as "FS Last Name"
    , c.role_nm
    , p.aflt_nm
    , p.eff_end_dt as "Party End Date"
    , c.eff_end_dt as "Period End Date"

    from vcntc_cntct_party p
    join vcntp_cntct_prd c
    on c.fk_cntc_party_ik = p.cntct_party_ik
    join vhppr_hp_purch u
    on u.hp_purch_ik = c.fk_cntr_purch_ik

    join (
    select fk_cntc_party_ik
    , hp_purch_ik
    , max(seq_nb) as max_seq_nb
    , max(eff_end_dt) as max_eff_end_dt
    from vcntp_cntct_prd vcntp
    join vhppr_hp_purch vhppr
    on vcntp.fk_cntr_purch_ik = vhppr.hp_purch_ik
    where eff_end_dt = ''' + convert(varchar(23),@effDate, 105) + '''
    group by fk_cntc_party_ik
    , hp_purch_ik
    ) vcntp2
    on vcntp2.hp_purch_ik = u.hp_purch_ik
    and vcntp2.fk_cntc_party_ik = c.fk_cntc_party_ik
    and vcntp2.max_eff_end_dt = c.eff_end_dt
    and vcntp2.max_seq_nb = c.seq_nb

    where p.eff_end_dt = ''' + convert(varchar(23),@effDate) + '''
    and c.eff_end_dt = ''' + convert(varchar(23),@effDate) + '''

    order by "FS Purchaser Name"
    , "FS Last Name"
    , "FS Purchaser ID"'')'


    PRINT @sql

    execute sp_executesql @sql

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So what is the output of PRINT @sql?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Posts
    193
    There isn't any. I guess what it's telling me is that it doesn't even get that far.

    ddave

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Paste this into notepad and you will see the problem:

    set @FSUserID = '''MYUSERNAME'''
    set @FSPW = '''MYPASSWORD'''
    set @AffiliateName = '''CLIENT COMPANY NAME%'''
    set @effDate = "'4000-12-31'"

    You are using three single quotes for the first three variables, but for the @effDate you are using a combination of a double-quote and a single-quote.

    Yes, we all get to dope-slap ourselves occasionally.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Posts
    193
    Ok, but I thought that is what you were telling me Friday. In any event this is what I have now. What I don't get is that when I copy and paste the output from PRINT @sql from Query Analyzer into TOAD which is calling an Oracle server it runs just fine. I get the data I am looking for. Isn't that what I am sending over? If I change the @effDate variable to datetime as this "declare @effDate as datetime" I get this error message and it doesn't even go over to Oracle:

    Server: Msg 241, Level 16, State 1, Line 13
    Syntax error converting datetime from character string.



    -- This is my script:
    -- declare variables:
    declare @FSUserID as nvarchar(30)
    declare @FSPW as nvarchar(30)
    declare @AffiliateName as nvarchar(50)
    declare @effDate as nvarchar(23)
    declare @sql as nvarchar(2000)


    -- set variable values:
    set @FSUserID = '''MYUSERID'''
    set @FSPW = '''MYPassword'''
    set @effDate = '''31-dec-4000'''


    -- place statement into variable
    set @sql = 'select *
    from openrowset(' + '''MSDAORA''' + '
    ,' + '''mYSERVERNAME'';' + @fsUserID + ';' + @fsPW + '
    ,' + '''select u.hp_purch_ik as "FS Purchaser ID"
    , upper(u.hp_purch_nm) as "FS Purchaser Name"
    , u.grp_size_cd
    , p.cntct_party_ik as "Broker No"
    , upper(p.frst_nm) as "FS First Name"
    , upper(p.lst_nm) as "FS Last Name"
    , c.role_nm
    , p.aflt_nm
    , p.eff_end_dt as "Party End Date"
    , c.eff_end_dt as "Period End Date"

    from vcntc_cntct_party p
    join vcntp_cntct_prd c
    on c.fk_cntc_party_ik = p.cntct_party_ik
    join vhppr_hp_purch u
    on u.hp_purch_ik = c.fk_cntr_purch_ik

    join (
    select fk_cntc_party_ik
    , hp_purch_ik
    , max(seq_nb) as max_seq_nb
    , max(eff_end_dt) as max_eff_end_dt
    from vcntp_cntct_prd vcntp
    join vhppr_hp_purch vhppr
    on vcntp.fk_cntr_purch_ik = vhppr.hp_purch_ik
    where eff_end_dt = ' + @effDate + '
    group by fk_cntc_party_ik
    , hp_purch_ik
    ) vcntp2
    on vcntp2.hp_purch_ik = u.hp_purch_ik
    and vcntp2.fk_cntc_party_ik = c.fk_cntc_party_ik
    and vcntp2.max_eff_end_dt = c.eff_end_dt
    and vcntp2.max_seq_nb = c.seq_nb

    where p.eff_end_dt = ' + @effDate + '
    and c.eff_end_dt = ' + @effDate + '

    order by "FS Purchaser Name"
    , "FS Last Name"
    , "FS Purchaser ID"'')'


    PRINT @sql

    execute sp_executesql @sql

    -----------------------------------
    -- This is the result:
    select *
    from openrowset('MyServerName'
    ,'mYSERVERNAME';'MyUSERID';'MYPW'
    ,'select u.hp_purch_ik as "FS Purchaser ID"
    , upper(u.hp_purch_nm) as "FS Purchaser Name"
    , u.grp_size_cd
    , p.cntct_party_ik as "Broker No"
    , upper(p.frst_nm) as "FS First Name"
    , upper(p.lst_nm) as "FS Last Name"
    , c.role_nm
    , p.aflt_nm
    , p.eff_end_dt as "Party End Date"
    , c.eff_end_dt as "Period End Date"

    from vcntc_cntct_party p
    join vcntp_cntct_prd c
    on c.fk_cntc_party_ik = p.cntct_party_ik
    join vhppr_hp_purch u
    on u.hp_purch_ik = c.fk_cntr_purch_ik

    join (
    select fk_cntc_party_ik
    , hp_purch_ik
    , max(seq_nb) as max_seq_nb
    , max(eff_end_dt) as max_eff_end_dt
    from vcntp_cntct_prd vcntp
    join vhppr_hp_purch vhppr
    on vcntp.fk_cntr_purch_ik = vhppr.hp_purch_ik
    where eff_end_dt = '31-dec-4000'
    group by fk_cntc_party_ik
    , hp_purch_ik
    ) vcntp2
    on vcntp2.hp_purch_ik = u.hp_purch_ik
    and vcntp2.fk_cntc_party_ik = c.fk_cntc_party_ik
    and vcntp2.max_eff_end_dt = c.eff_end_dt
    and vcntp2.max_seq_nb = c.seq_nb

    where p.eff_end_dt = '31-dec-4000'
    and c.eff_end_dt = '31-dec-4000'

    order by "FS Purchaser Name"
    , "FS Last Name"
    , "FS Purchaser ID"')

    Server: Msg 170, Level 15, State 1, Line 29
    Line 29: Incorrect syntax near '31'.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I suggested that you change the method of quoting the reference to @effdate, but for some reason you modified the actual string value instead. Of course it errors out the same as before.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Posts
    193
    OK,

    Next attempt. I believe this is what you suggested?


    -- declare variables:
    declare @FSUserID as nvarchar(30)
    declare @FSPW as nvarchar(30)
    declare @AffiliateName as nvarchar(50)
    declare @effDate as datetime
    declare @sql as nvarchar(2000)


    -- set variable values:
    set @FSUserID = '''MYUSERID'''
    set @FSPW = '''MYPASSWORD'''
    set @AffiliateName = '''A CLIENT NAME STRING%'''
    set @effDate = '4000-12-31' ----------------------< I changed this here


    -- place statement into variable
    set @sql = 'select *
    from openrowset(' + '''MSDAORA''' + '
    ,' + '''SERVERNAME'';' + @fsUserID + ';' + @fsPW + '
    ,' + '''select u.hp_purch_ik as "FS Purchaser ID"
    , upper(u.hp_purch_nm) as "FS Purchaser Name"
    , u.grp_size_cd
    , p.cntct_party_ik as "Broker No"
    , upper(p.frst_nm) as "FS First Name"
    , upper(p.lst_nm) as "FS Last Name"
    , c.role_nm
    , p.aflt_nm
    , p.eff_end_dt as "Party End Date"
    , c.eff_end_dt as "Period End Date"

    from vcntc_cntct_party p
    join vcntp_cntct_prd c
    on c.fk_cntc_party_ik = p.cntct_party_ik
    join vhppr_hp_purch u
    on u.hp_purch_ik = c.fk_cntr_purch_ik

    join (
    select fk_cntc_party_ik
    , hp_purch_ik
    , max(seq_nb) as max_seq_nb
    , max(eff_end_dt) as max_eff_end_dt
    from vcntp_cntct_prd vcntp
    join vhppr_hp_purch vhppr
    on vcntp.fk_cntr_purch_ik = vhppr.hp_purch_ik
    where eff_end_dt = ''' + convert(varchar(23),@effDate, 105) + '''
    group by fk_cntc_party_ik
    , hp_purch_ik
    ) vcntp2
    on vcntp2.hp_purch_ik = u.hp_purch_ik
    and vcntp2.fk_cntc_party_ik = c.fk_cntc_party_ik
    and vcntp2.max_eff_end_dt = c.eff_end_dt
    and vcntp2.max_seq_nb = c.seq_nb

    where p.eff_end_dt = ''' + convert(varchar(23),@effDate) + '''
    and c.eff_end_dt = ''' + convert(varchar(23),@effDate) + '''

    order by "FS Purchaser Name"
    , "FS Last Name"
    , "FS Purchaser ID"'')'


    PRINT @sql

    execute sp_executesql @sql


    ---------------------------------------

    Results:

    select *
    from openrowset('MSDAORA'
    ,'SERVERNAME';'MYUSERID';'MYPASSWORD'
    ,'select u.hp_purch_ik as "FS Purchaser ID"
    , upper(u.hp_purch_nm) as "FS Purchaser Name"
    , u.grp_size_cd
    , p.cntct_party_ik as "Broker No"
    , upper(p.frst_nm) as "FS First Name"
    , upper(p.lst_nm) as "FS Last Name"
    , c.role_nm
    , p.aflt_nm
    , p.eff_end_dt as "Party End Date"
    , c.eff_end_dt as "Period End Date"

    from vcntc_cntct_party p
    join vcntp_cntct_prd c
    on c.fk_cntc_party_ik = p.cntct_party_ik
    join vhppr_hp_purch u
    on u.hp_purch_ik = c.fk_cntr_purch_ik

    join (
    select fk_cntc_party_ik
    , hp_purch_ik
    , max(seq_nb) as max_seq_nb
    , max(eff_end_dt) as max_eff_end_dt
    from vcntp_cntct_prd vcntp
    join vhppr_hp_purch vhppr
    on vcntp.fk_cntr_purch_ik = vhppr.hp_purch_ik
    where eff_end_dt = '31-12-4000'
    group by fk_cntc_party_ik
    , hp_purch_ik
    ) vcntp2
    on vcntp2.hp_purch_ik = u.hp_purch_ik
    and vcntp2.fk_cntc_party_ik = c.fk_cntc_party_ik
    and vcntp2.max_eff_end_dt = c.eff_end_dt
    and vcntp2.max_seq_nb = c.seq_nb

    where p.eff_end_dt = 'Dec 31 4000 12:00AM'
    and c.eff_end_dt = 'Dec 31 4000 12:00AM'

    order by "FS Purchaser Name"
    , "FS Last Name"
    , "FS Purchaser ID"')
    Server: Msg 170, Level 15, State 1, Line 29
    Line 29: Incorrect syntax near '31'.

  12. #12
    Join Date
    Feb 2004
    Posts
    193
    Here is another mod. I think I am getting close bec at least w/ this attempt it looks like it is just a formatting issue on the Oracle server I am sending this to. I declared the @effDate variable as nvarchar(23). So it looks like it is receiving the query but the date format is not correct.

    Script:

    -- declare variables:
    declare @FSUserID as nvarchar(30)
    declare @FSPW as nvarchar(30)
    declare @AffiliateName as nvarchar(50)
    declare @effDate as nvarchar(23) --< Modified datatype
    declare @sql as nvarchar(2000)


    -- set variable values:
    set @FSUserID = '''MYUSERID'''
    set @FSPW = '''MYPASSWORD'''
    set @AffiliateName = '''A CLIENT NAME%'''
    set @effDate = '''4000-12-31'''


    -- place statement into variable
    set @sql = 'select *
    from openrowset(' + '''MSDAORA''' + '
    ,' + '''SERVERNAME'';' + @fsUserID + ';' + @fsPW + '
    ,' + '''select u.hp_purch_ik as "FS Purchaser ID"
    , upper(u.hp_purch_nm) as "FS Purchaser Name"
    , u.grp_size_cd
    , p.cntct_party_ik as "Broker No"
    , upper(p.frst_nm) as "FS First Name"
    , upper(p.lst_nm) as "FS Last Name"
    , c.role_nm
    , p.aflt_nm
    , p.eff_end_dt as "Party End Date"
    , c.eff_end_dt as "Period End Date"

    from vcntc_cntct_party p
    join vcntp_cntct_prd c
    on c.fk_cntc_party_ik = p.cntct_party_ik
    join vhppr_hp_purch u
    on u.hp_purch_ik = c.fk_cntr_purch_ik

    join (
    select fk_cntc_party_ik
    , hp_purch_ik
    , max(seq_nb) as max_seq_nb
    , max(eff_end_dt) as max_eff_end_dt
    from vcntp_cntct_prd vcntp
    join vhppr_hp_purch vhppr
    on vcntp.fk_cntr_purch_ik = vhppr.hp_purch_ik
    where eff_end_dt = ''' + convert(varchar(23),@effDate, 105) + '''
    group by fk_cntc_party_ik
    , hp_purch_ik
    ) vcntp2
    on vcntp2.hp_purch_ik = u.hp_purch_ik
    and vcntp2.fk_cntc_party_ik = c.fk_cntc_party_ik
    and vcntp2.max_eff_end_dt = c.eff_end_dt
    and vcntp2.max_seq_nb = c.seq_nb

    where p.eff_end_dt = ''' + convert(varchar(23),@effDate, 105) + '''
    and c.eff_end_dt = ''' + convert(varchar(23),@effDate, 105) + '''

    order by "FS Purchaser Name"
    , "FS Last Name"
    , "FS Purchaser ID"'')'


    PRINT @sql

    execute sp_executesql @sql

    -------------------------------------

    Results:

    select *
    from openrowset('MSDAORA'
    ,'SERVERNAME';'MYUSERID';'MYPASSWORD'
    ,'select u.hp_purch_ik as "FS Purchaser ID"
    , upper(u.hp_purch_nm) as "FS Purchaser Name"
    , u.grp_size_cd
    , p.cntct_party_ik as "Broker No"
    , upper(p.frst_nm) as "FS First Name"
    , upper(p.lst_nm) as "FS Last Name"
    , c.role_nm
    , p.aflt_nm
    , p.eff_end_dt as "Party End Date"
    , c.eff_end_dt as "Period End Date"

    from vcntc_cntct_party p
    join vcntp_cntct_prd c
    on c.fk_cntc_party_ik = p.cntct_party_ik
    join vhppr_hp_purch u
    on u.hp_purch_ik = c.fk_cntr_purch_ik

    join (
    select fk_cntc_party_ik
    , hp_purch_ik
    , max(seq_nb) as max_seq_nb
    , max(eff_end_dt) as max_eff_end_dt
    from vcntp_cntct_prd vcntp
    join vhppr_hp_purch vhppr
    on vcntp.fk_cntr_purch_ik = vhppr.hp_purch_ik
    where eff_end_dt = ''4000-12-31''
    group by fk_cntc_party_ik
    , hp_purch_ik
    ) vcntp2
    on vcntp2.hp_purch_ik = u.hp_purch_ik
    and vcntp2.fk_cntc_party_ik = c.fk_cntc_party_ik
    and vcntp2.max_eff_end_dt = c.eff_end_dt
    and vcntp2.max_seq_nb = c.seq_nb

    where p.eff_end_dt = ''4000-12-31''
    and c.eff_end_dt = ''4000-12-31''

    order by "FS Purchaser Name"
    , "FS Last Name"
    , "FS Purchaser ID"')
    Server: Msg 7320, Level 16, State 2, Line 1
    Could not execute query against OLE DB provider 'MSDAORA'.
    [OLE/DB provider returned message: ORA-01847: day of month must be between 1 and last day of month
    ]
    OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandText::Execute returned 0x80040e07].

  13. #13
    Join Date
    Feb 2004
    Posts
    193
    Hallelujah! I got it. The format had to be as follows:
    set @effDate = '''31-Dec-4000'''

    Finally, I got this to go through. I now have data returned. I hope this helps others struggling with Dynamic SQL syntax.

    Thanks for your time in looking into this Blindman.

    ddave

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Glad you got it to work.
    But now I'm kind of wondering why you bothered putting @EffDate into a variable, when you are treating it like a constant. That resulted in Oracle converting it to its own internal datetime value and formatting.
    I think you could save yourself some trouble by just hard-coding '4000-12-31' in place of @EffDate throughout your code. Either that or just declare @EffDate as a string rather than a datetime variable.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Feb 2004
    Posts
    193
    I am still learning Dynamic SQL. I didn't know the syntax to make that happen. Yes, I do believe that would be best if the date were a literal value bec this value won't change for active records.

    ddave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •