Solved

SYSDATE without seperators

Posted on 2003-10-28
6
1,556 Views
Last Modified: 2007-12-19
Hi

I am a newbie. I have a requirement to insert the system date_time in the format of

yyyymmddhhmmss

However since I might possibly be doing a string comparison, it would be nice to just insert it as a string. Though is it not imperative.

Kindly help.
0
Comment
Question by:zorrinn
6 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 20 total points
ID: 9635036
use to_char()
try this:

select to_char(sysdate, 'YYYYMMDDHHMMSS') from dual;
0
 
LVL 4

Expert Comment

by:Vinay_dba
ID: 9635041
Use
select to_char(sysdate,'yyyymmddhhmmss') from dual;

It will give you output as string
20031028111026
0
 
LVL 7

Expert Comment

by:Bigfam5
ID: 9635108
It always best to have DATE columns as dates and when comparing use the to_date on the 'comparing value' and to_char to display in different formats

insert into my_dates (date_column) values (sysdate);

select * from my_dates
where date_column = to_char('20031026080030','yyyymmddhh24miss')
/

to_display in a specific format

select to_char(date_column, 'yyyymmddhh24miss') from my_dates
/

Note: that it is  MI for minutes and not MM (months)
0
 

Expert Comment

by:Softchoice
ID: 9636841
I would always advise inserting dates as datatype DATE (as line 2 below indicates). If you need to do string comparisons later...do those by converting to strings on retrieval (as line 3 below indicates)

  create table date_insert_test (test_date date);
  insert into date_insert_test (test_date) values (to_date('19850215123015','yyyymmddhhmiss'));
  select * from date_insert_test where to_char(test_date,'YYYY') = '1985';
  drop table date_insert_test;

Hope this helps.



0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question