Updating date field in oracle
All your example does is get the sysdate, convert it into char, insert it into a single column of type varchar, select it back out again, and convert it to a date. If you use the fm (format modifier) mask -- you'll get what you want. The problem is that the data in the varchar2 columns may not be valid dates and times. garbage in, garbage can write a PLSQL to try many different date formats, catching the exception when a string fails -- returning a date if it converted, NULL otherwise. 'One Hundred Twenty-Five' and i found some nice select that says : SELECT to_char(to_date(125,'J'),'JSP') FROM DUAL ;and it work greatbut1- i want the output be in arabic language i try 'NLS_DATE_LANGUAGE = EGYPTIAN' but no use2- i want to understand what 'J' mean in to_date function and what 'JSP' mean in to_char function Hi Tom, After database migration.
Using to_date with the concatenation operator is the most obvious solution to the original question. COM @test '"'
It uses fixed-length fields of 7 bytes, each corresponding to century, year, month, day, hour, minute, and second to store date data.
Also, we are using the default of AMERICA for NLS_TERRITORY at instance level. If fmt is 'J', for Julian, then char must be an integer.
In a nutshell, I was expecting either the year 1952 (if Oracle was taking database level setting) or an ORA- error (if Oracle was taking session level setting) for not supplying the 4 digits for year. The default date format is determined implicitly by the NLS_TERRITORY initialization parameter, or can be set explicitly by the NLS_DATE_FORMAT parameter.
Many thanks if it has leading zeroes, it is "not a number"Just to add that the questioner can put in a check constraint to ensure that the string only contains the characters 0123456789.
And with regular expressions in 10g, it becomes a lot easier to enforce format masks on VARCHAR2 data.