Convert Access dates to Oracle format

[carl.barrett@newcastle.gov.uk]

Hi,

I am writing to a text file the records in an access table that is to
be loaded into an Oracle system.

However, I have been told that the date fields in the records I have
exported to the text file that are not compatible with oracle. The
format looks like: 01/01/2006 and I need it to look like 01-JAN-2006.

I have tried using the format function and the variable still shows it
as 01/01/2006.

The text file is generated by clicking from a button on a form which
contains the string/date variables and a loop with the write lines. I
am not on my work PC at the minute so can't show what I've created, but
can post it later if needed.

Can anyone give me an idea of how to get this sorted.

Cheers.

[Gox]

Re: Convert Access dates to Oracle format

Try this function:

Function ToAmDate(dDatum As Variant) As Variant
If IsNull(dDatum) Then
ToAmDate = Null
Exit Function
End If
ToAmDate = LTrim(str(Day(d Datum))) & "-" &
LTrim(str(Month (dDatum))) & "-" & LTrim(str(Year( dDatum)))
End Function


carl.barrett@ne wcastle.gov.uk wrote:[color=blue]
> Hi,
>
> I am writing to a text file the records in an access table that is to
> be loaded into an Oracle system.
>
> However, I have been told that the date fields in the records I have
> exported to the text file that are not compatible with oracle. The
> format looks like: 01/01/2006 and I need it to look like 01-JAN-2006.
>
> I have tried using the format function and the variable still shows it
> as 01/01/2006.
>
> The text file is generated by clicking from a button on a form which
> contains the string/date variables and a loop with the write lines. I
> am not on my work PC at the minute so can't show what I've created, but
> can post it later if needed.
>
> Can anyone give me an idea of how to get this sorted.
>
> Cheers.
>[/color]

[carl.barrett@newcastle.gov.uk]

Re: Convert Access dates to Oracle format

Thanks for the response. However it still returns the month as a
number and not in character form e.g. 24-11-2004 instead of 24-NOV-2004

Can you help again?

Cheers.


Gox wrote:[color=blue]
> Try this function:
>
> Function ToAmDate(dDatum As Variant) As Variant
> If IsNull(dDatum) Then
> ToAmDate = Null
> Exit Function
> End If
> ToAmDate = LTrim(str(Day(d Datum))) & "-" &
> LTrim(str(Month (dDatum))) & "-" & LTrim(str(Year( dDatum)))
> End Function
>
>
> carl.barrett@ne wcastle.gov.uk wrote:[color=green]
> > Hi,
> >
> > I am writing to a text file the records in an access table that is to
> > be loaded into an Oracle system.
> >
> > However, I have been told that the date fields in the records I have
> > exported to the text file that are not compatible with oracle. The
> > format looks like: 01/01/2006 and I need it to look like 01-JAN-2006.
> >
> > I have tried using the format function and the variable still shows it
> > as 01/01/2006.
> >
> > The text file is generated by clicking from a button on a form which
> > contains the string/date variables and a loop with the write lines. I
> > am not on my work PC at the minute so can't show what I've created, but
> > can post it later if needed.
> >
> > Can anyone give me an idea of how to get this sorted.
> >
> > Cheers.
> >[/color][/color]

[Gox]

Re: Convert Access dates to Oracle format

Try this:

Function ToAmDate(dDatum As Variant) As Variant
If IsNull(dDatum) Then
ToAmDate = Null
Exit Function
End If
ToAmDate = Format(CDate(dD atum), "dd-mmm-yyyy")
End Function

carl.barrett@ne wcastle.gov.uk wrote:[color=blue]
> Thanks for the response. However it still returns the month as a
> number and not in character form e.g. 24-11-2004 instead of 24-NOV-2004
>
> Can you help again?
>
> Cheers.
>[/color]

[carl.barrett@newcastle.gov.uk]

Re: Convert Access dates to Oracle format

Works a treat.

Cheers.

[Tim Marshall]

Re: Convert Access dates to Oracle format

carl.barrett@ne wcastle.gov.uk wrote:
[color=blue]
> Thanks for the response. However it still returns the month as a
> number and not in character form e.g. 24-11-2004 instead of 24-NOV-2004[/color]

I'm curious as to why your Oracle admin isn't using that most basic of
Oracle concepts, the date picture.

Gox is giving you good functions, but in reality, you DON'T NEED THEM!!!!

to_date('column _with_access_da te', 'dd/mm/yyyy')

To illustrate this, execute the following in SQL Plus (the Oracle guys
will know what this is):

SQL> select to_date('24/11/2004', 'dd/mm/yyyy') from dual;

TO_DATE('
---------
24-NOV-04

SQL>

A good Oracle admin should know how to use SQLLDR and a control file to
take your text file into Oracle. Part of the control file will use a
proper date picture to deal with any kind of date.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me