Advertisement
Advertisement


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...

2011/09/24
1
45
9/24/2011 8:10:30 AM

Accepted 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.

2019/05/19
38
5/19/2019 8:11:27 AM


Try this:

...(to_date('2011/04/22 08:30:00', 'yyyy/mm/dd hh24:mi:ss'));

2011/09/24

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'));
2018/04/08

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'));
2020/04/27

Source: https://stackoverflow.com/questions/7536628
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]