SQL & PL/SQL

I have Oracle 11gR2 x64 running on my Win7 Enterprise x64 personal system. I'm trying to convert the TSQL to create a database in SQL Server 2008 Express for my C# class to Oracle, and I've run into a "interesting" problem loading date data.

The SQL Server 2008 TSQL InvoiceDate column in the Invoices table has datatype DATETIME:

*[InvoiceDate] [datetime] NOT NULL,*

which Oracle does not support. The Oracle InvoiceDate column in the MMAB_Invoices table has datatype DATE:

InvoiceDate DATE NOT NULL,

The fun begins with the TSQL INSERT statements (for example):

INSERT [dbo].[Invoices] ([InvoiceID], [CustomerID], [InvoiceDate], [ProductTotal], [SalesTax], [Shipping], [InvoiceTotal]) VALUES (18, 20, CAST(0x00009CFD00000000 AS DateTime), 151.0000, 11.3300, 6.2500, 168.5800)

As you can see, whoever wrote the load script thought it would be totally awesome to convert hex (binary?) data to generate a date, instead of using data readable by a human being.

BTW, the date generated is 2010-01-13 00:00:00.000.

After Reading The Fabulous Manual and searching the OTN Discussion Fora, what I came up with is

INSERT INTO MMAB_Invoices (CustomerID, InvoiceDate, ProductTotal, SalesTax, Shipping, InvoiceTotal) VALUES (20, TO_DATE(UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('00009CFD00000000')), 'YYYY-MM-DD HH:MI:SS'), 151.0000, 11.3300, 6.2500, 168.5800);

which returned

Error starting at line 846 in command:
INSERT INTO MMAB_Invoices (CustomerID, InvoiceDate, ProductTotal, SalesTax, Shipping, InvoiceTotal) VALUES (20, TO_DATE(UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('00009CFD00000000')), 'YYYY-MM-DD HH:MI:SS'), 151.0000, 11.3300, 6.2500, 168.5800)
Error report:
SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
*01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"*
**Cause: Illegal year entered*
**Action: Input year in the specified range*

The PK InvoiceID is automatically generated by a BEFORE INSERT trigger that uses a sequence.

Suggestions?

Thanks.


P.S. Happy New Year!!!