how to insert date and time in oracle

Question

Im having trouble inserting a row in my table  Here is the insert statement and table creation  This is part of a uni assignment hence the simplicity  what am i doing wrong  Im using oracle SQL developer Version 3 0 04    The problem i am having is that it is only inserting the dd mon yy but not the time  How do i get it to insert the time as well   INSERT INTO WORKON  STAFFNO CAMPAIGNTITLE DATETIME HOURS  VALUES   102   Machanic Summer Savings  TO DATE  22 April 2011 8 30 00AM   DD MON YY HH MI SSAM    3      CREATE TABLE WorkOn       StaffNo        NCHAR 4       CampaignTitle  VARCHAR 50       DateTime       DATE      Hours          VARCHAR 2        Thanks for the help   EDIT  This is making no sense  i enter just a time in the field to test if time is working and it outputs a date WTF  This is really weird i may not use a date field and just enter the time in  i realise this will result in issues manipulating the data but this is making no sense

User · Answer

Just use TO DATE   function to convert string to DATE   For Example   create table Customer         CustId int primary key         CustName varchar 20          DOB date    insert into Customer values 1  Vishnu   TO DATE  1994 12 16 12 00 00    yyyy mm dd hh mi ss

User · Answer

create table Customer         CustId int primary key         CustName varchar 20          DOB date    insert into Customer values 1  kingle   TO DATE  1994-12-16 12 00 00    yyyy-MM-dd hh mi ss

User · Answer

You are doing everything right by using a to date function and specifying the time  The time is there in the database  The trouble is just that when you select a column of DATE datatype from the database  the default format mask doesn t show the time  If you issue a  alter session set nls date format    dd MON yyyy hh24 mi ss   or something similar including a time component  you will see that the time successfully made it into the database

User · Answer

You can use   insert into table name  date field  values  TO DATE  2003 05 03 21 02 44    yyyy mm dd hh24 mi ss       Hope it helps

User · Answer

Try this       to date  2011 04 22 08 30 00    yyyy mm dd hh24 mi ss

[oracle] how to insert date and time in oracle?

Examples related to oracle