Date Conversion Error - ORA-01861: literal format matching

[bgs_vb]

Hi OnlineGurus,

my routine is like this:

MaxVal = "1753-01-01 00:00:00"

Temp1 = LEFT(MaxVal,10)
Temp2 = RIGHT(MaxVal,8)

MaxDate = OCONV(Temp1, "D/YMD[4,2,2]")
MaxTime = OCONV(Temp2, "MTS")
Ans = MaxDate:" ":MaxTime

I am getting the literal error. my doubt is whether we can use both date and time withing a single string and what format does oracle use. how to check the format.

Thanks in advance

Regards,
Vindy

[ArndW]

Vindy,

The OCONV() functions you have will convert an internal representation to an external one, so before you can convert your raw strings to the appropriate format you will need to convert them to internal format. Assuming you don't need any data error correction you can use:

Code: Select all

Temp1 = ICONV(LEFT(MaxVal,10),'D4-YMD')
Temp2 = ICONV(RIGHT(MaxVal,8),'MTS')

[chulett]

Actually, if you are using an OCI stage and a datatype of Timestamp - you've already got it in the proper format.

[bgs_vb]

Hi Craig,

here i am using ODBC stage, not OCI stage.

Vindy

[chulett]

Yuck, whatever for. Still, I think you'll find using a Timestamp type and your current format will work - even in ODBC.

[kwwilliams]

Craig,

you are correct timestamp is the correct format even using ODBC.