Convert string to date, for use in SQL Server...

[gdrenfrew]

Trying to get a date out of a table, unforunately it is in the format "dd.mm.yyyy". Can someone suggest how I can change this into "dd-Mon-yyy"?

It needs to be in a suitable format for inserting into SQL Server, and I've found that this format works.

I've tried the following:
to_char(translate("DOCDATE",'.','/'),'dd-Mon-yyyy HH24:MI:SS'), but it says it's an invalid number. If I don't use the translate function, to_char doesn't think it's a date.
I'm stuck!

 

[sem]

If your field originally is of DATE data type, you may explicitly convert it to almost whatever you need using TO_CHAR function as in Oracle Dates are stored in some internal format and then are just converted to be displayed according to current NLS settings.

In your case you may use

TO_CHAR(field, 'dd-Mon-yyy')

If that field is string ([VAR]CHAR[2]), then you may convert it to date and then apply another mask:

TO_CHAR( TO_DATE(field, 'dd.mm.yyyy'), 'dd-Mon-yyy')

Regards, Dima

http://seminihin.narod.ru

 

[gdrenfrew]

Thanks Sem, that's great. Worked a treat.