| Author |
Topic |
|
ozymandias
Starting Member
6 Posts |
 Posted - 2009-06-16 : 08:22:55
|
| This is my first question so please don't shoot me :) I can't get my head around how to use dates and times in Oracle, apparently the data type date is supposed to be able to store a time as well, but when I view the table it only shows me the date. On the recommendation on a website I used to_date to insert the date and time values, like this:INSERT into bokningar(StartTid, BokTid)values(to_date('2009/06/22:10:00:00am', 'yyyy/mm/dd:hh:mi:ssam'), to_date('2009/06/22:09:05:00am', 'yyyy/mm/dd:hh:mi:ssam'));When I select *from bokningar this is what I get:STARTTID BOKTID22-Jun-09 22-Jun-09 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-16 : 08:57:41
|
Are you sure there should be a ":" separator between date and time portions?The with space as separator between date and time portions.INSERT into bokningar(StartTid, BokTid)values(to_date('2009/06/22 10:00:00am', 'yyyy/mm/dd:hh:mi:ssam'), to_date('2009/06/22 09:05:00am', 'yyyy/mm/dd:hh:mi:ssam'));Also, doesn't the to_date function remove the time portion for you?INSERT into bokningar(StartTid, BokTid)values('2009/06/22 10:00:00am', '2009/06/22 09:05:00am');
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
ozymandias
Starting Member
6 Posts |
Posted - 2009-06-16 : 09:58:46
|
| Thanks for the reply!I thought it looked strange too, but deleting ":" makes no difference, and deleting to_date along with the <format> string like in your example gives me the error message: "literal does not match format string"? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-16 : 11:25:33
|
You have to change the format string accordingly.INSERT into bokningar(StartTid, BokTid)values(to_date('2009/06/22 10:00:00', 'yyyy/mm/dd hh:mi:ss'), to_date('2009/06/22 09:05:00', 'yyyy/mm/dd hh:mi:ss'));
E 12°55'05.63"N 56°04'39.26" |
|
|
|
ozymandias
Starting Member
6 Posts |
Posted - 2009-06-16 : 12:19:16
|
| I did that, but it makes no difference, when I select *from bokningar it still shows me the date only and no time. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-16 : 12:36:14
|
What datatype is StartTid and BokTid columns?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
ozymandias
Starting Member
6 Posts |
Posted - 2009-06-16 : 12:55:00
|
| It's the "date" data type, which is supposed to store date as well as time in Oracle according to what I have read? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-16 : 12:56:56
|
No. Date are dates only.You should probably use DATETIME or DATETIME2.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
ozymandias
Starting Member
6 Posts |
Posted - 2009-06-16 : 13:37:23
|
| I read it here:http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html"Rather than using two separate entities, date and time, Oracle only uses one, DATE. The DATE type is stored in a special internal format that includes not just the month, day, and year, but also the hour, minute, and second."I am not sure about how to use these datatypes, and have problems finding clear information about basic syntax for them. When I try to alter the table using datetime or datetime2 instead of date, I get the error message "invalid datatype". |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-16 : 13:50:59
|
Maybe you should try out www.dbforums.com since this is a Microsoft SQL Server dedicated forum.www.dbforums.com has a special Oracle forum.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
ozymandias
Starting Member
6 Posts |
Posted - 2009-06-16 : 13:54:26
|
| Ah OK, I didn't realize that. Thanks for the links and the help! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-16 : 13:55:46
|
When you do get an answer, please post solution here too.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
Time in Oracle