SQL questions on altering a table??

Question

I'm trying to insert a new row, but encounter a few problems here.

This is the table I'm going to insert a row to:

http://img3.imageshack.us/img3/7759/appeals2.jpg

I have to insert crime_ID = 25344031, filing date = 02 13 2009, hearing date = 02 27 2009

I'm having problem to insert the date, and the table only allow me to enter 7 digit date as the format of 12-Dec-2010.

I use this command but doesn't work. Does the date datatype only accept allow 7 digits?
ALTER TABLE appeals
MODIFY (FILING_DATE DATE(8))

The table structure is follow:
http://img249.imageshack.us/img249/6919/appealsf.jpg

What can I alter so that I can insert the row??

The complete questions is below if you are confused with what I'm saying:

An INSERT statement is needed to support users adding a new appeal. Create an INSERT statement using substitution variables. Note that users will be entering dates in the format of a two-digit month, a two-digit day, and a four-digit year, such as "12 17 2009". In addition, a sequence named APPEALS_ID_SEQ exists to supply values for the Appeal_ID column, and the default setting for the Status column should take effect (that is, the DEFAULT option on the column should be used). Test the statement by adding the following appeal: crime_ID = 25344031, filing date = 02 13 2009, and hearing date = 02 27 2009.

Any helps or advices is appreciated......

Answer 1

The to_date function converts a character string into the date format you specify. the TO_CHAR function is used in select statements to translate a date into a character string in the format you want. Dates are stored internally in Oracle as a fixed 7 bytes and contain the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does NOT have fractional seconds or a time zone. So depending on your nls_date or nls_language settings or just to be sure you need to change the input and output from the default format to the one of your choice.



Therefore if you want to enter the date in the format mm dd yyyy, that's the format you need to use with the to_date function i.e

INSERT INTO my_table (crime_id,filing_date,hearing_date)

VALUES (25344031, filing date( '02 13 2009','mm dd yyyy'), to_date('02 27 2009','mm dd yyyy')



For more help on SQL see http://www.asktheoracle.net/oracle-tutorials.html

Answer 2

You need to use whatever conversion function your DBMS has to cast the string into a proper date type value. For instance, in Oracle, you'd specify



TO_DATE (, "mm dd yyyy")



as the date value to be inserted. For SQL Server, you could use SUBSTRING to insert slashes so that it's in proper date format and then CAST it into an actual date value.

Answer 3

from the command-line: sqlplus username/password @scriptname eg: sqlplus scott/tiger @create_tables.sq. Your comments ought at the start REM or -- you're able to additionally prefer to comprise go out; on the top except you prefer to stay in sqlplus on the top of the script.