Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    7

    Unanswered: Access to Oracle DATE FORMATTING

    I'm unfamiliar with Visual Basic code, but I need to select a date value (creation_date) from an Access database and insert it into Oracle within Visual Basic. Here's the code:

    "SELECT rtu_nb, rtu_name, type, service, creation_date " & _
    "FROM rtu WHERE service IN('com_sctm', 'com_dlms', 'com_fnp', 'com_iec', 'com_iec1107', 'com_vec') ORDER BY rtu_nb")

    "INSERT INTO C2000Rtu (rtu_nb, rtu_name, type, service, creation_date) " & _
    "VALUES (" & rs.Fields("rtu_nb").Value & ", '" & tmpRtuName & "'," & rs.Fields("type").Value & ", '" & rs.Fields("service").Value & "', '" & rs.Fields("creation_date").Value & "')"

    RTU is a table in Access Database, C2000Rtu is a table in Oracle Database. All these extra characters other than the field names is a bit confusing. So I don't know where/how to apply a function on creation_date. But basically, I'm getting errors because I think the date format is different. Any thoughts?
    Last edited by ezrasuncle; 02-04-04 at 04:57.

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: Access to Oracle DATE FORMATTING

    Originally posted by ezrasuncle
    I'm unfamiliar with Visual Basic code, but I need to select a date value (creation_date) from an Access database and insert it into Oracle within Visual Basic. Here's the code:

    "SELECT rtu_nb, rtu_name, type, service, creation_date " & _
    "FROM rtu WHERE service IN('com_sctm', 'com_dlms', 'com_fnp', 'com_iec', 'com_iec1107', 'com_vec') ORDER BY rtu_nb")

    "INSERT INTO C2000Rtu (rtu_nb, rtu_name, type, service, creation_date) " & _
    "VALUES (" & rs.Fields("rtu_nb").Value & ", '" & tmpRtuName & "'," & rs.Fields("type").Value & ", '" & rs.Fields("service").Value & "', '" & rs.Fields("creation_date").Value & "')"

    RTU is a table in Access Database, C2000Rtu is a table in Oracle Database. All these extra characters other than the field names is a bit confusing. So I don't know where/how to apply a function on creation_date. But basically, I'm getting errors because I think the date format is different. Any thoughts?
    ALTER SESSION SET NLS_DATE_FORMAT = 'The same format as in VB'

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Alternatively,

    Insert into orcl_table values (to_date(DATE_A), 'DATE_A-FORMAT');

    where DATE_A = 11/31/1994 and DATA_A-FORMAT = 'MM/DD/YYYY'
    Last edited by r123456; 02-04-04 at 06:14.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    7

    Re: Access to Oracle DATE FORMATTING

    Originally posted by ezrasuncle
    "INSERT INTO C2000Rtu (rtu_nb, rtu_name, type, service, creation_date) " & _
    "VALUES (" & rs.Fields("rtu_nb").Value & ", '" & tmpRtuName & "'," & rs.Fields("type").Value & ", '" & rs.Fields("service").Value & "', '" & rs.Fields("creation_date").Value & "')"
    Where does this format go? I don't understand this "& rs.fields..." blah blah. I've tried

    '" & rs.Fields("to_date(creation_date, 'DD.MM.YYYY')")Value & "')"

    but nothing. As well as many other combinations. If I get rid of everything and just put a date in, like '01.01.1999', it works. Other attempts at code, I get things like "comma missing" or "column not allowed here".

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Dim str As String
    str = "insert into table (x) values(to_date(" & dateString & ", 'DD.MM.YYYY'));"
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Feb 2004
    Location
    Hong Kong
    Posts
    15

    Re: Access to Oracle DATE FORMATTING

    In VB you have declared recordset (rs). When you are selecting data from ACCESS table the date field type would be dd/mm/yy based on your computer date. This date format can't be inserted into oracle easily unless you use different date format. Before inserting into oracle table change the date format to dd-mon-yy and try to insert. It will work. Or check with VB whether you can you use to_Char function in VB.

    Hope it works...


    Originally posted by ezrasuncle
    I'm unfamiliar with Visual Basic code, but I need to select a date value (creation_date) from an Access database and insert it into Oracle within Visual Basic. Here's the code:

    "SELECT rtu_nb, rtu_name, type, service, creation_date " & _
    "FROM rtu WHERE service IN('com_sctm', 'com_dlms', 'com_fnp', 'com_iec', 'com_iec1107', 'com_vec') ORDER BY rtu_nb")

    "INSERT INTO C2000Rtu (rtu_nb, rtu_name, type, service, creation_date) " & _
    "VALUES (" & rs.Fields("rtu_nb").Value & ", '" & tmpRtuName & "'," & rs.Fields("type").Value & ", '" & rs.Fields("service").Value & "', '" & rs.Fields("creation_date").Value & "')"

    RTU is a table in Access Database, C2000Rtu is a table in Oracle Database. All these extra characters other than the field names is a bit confusing. So I don't know where/how to apply a function on creation_date. But basically, I'm getting errors because I think the date format is different. Any thoughts?

Posting Permissions

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