I have a date that is received as text in Jan 1 2002 12:00AM format. Can anyone give me any ideas how to format that in oracle format i.e. 01-JAN-02
Usually you can convert dates in SQL - using the convert command....such as
dob=convert(char(8),date_of_birth,112)
This converts from Jan 1 2002 12:00AM to 20020101
I'm not sure what all the other formats are....but I would think there's one from this format to 01-JAN-2002. And I'm not sure of your usage of this...so it might not be appropriate.
If you want to do it in Unix..you could use something like:
the_date='Jan 25 2002 12:00AM'
the_month=`echo $the_date | cut -c1-3 | tr "[:lower:]" "[:upper:]"`
day=`echo $the_date | cut -c5-6`
the_year=`echo $the_date | cut -c8-11`
new_date="$day-$the_month-$the_year"
echo $new_date
Gives: 25-JAN-2002
The only shortcoming of this is that it won't fill the '0' for dates 1-9.....but I'll leave you to figure that out if you want to use it.