SYSDATE without seperators

Posted on 2003-10-28
Last Modified: 2007-12-19

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


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.
Question by:zorrinn
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 23

Accepted Solution

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

select to_char(sysdate, 'YYYYMMDDHHMMSS') from dual;

Expert Comment

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

It will give you output as string

Expert Comment

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)

Expert Comment

ID: 9636841
I would always advise inserting dates as datatype DATE (as line 2 below indicates). If you need to do string comparisons 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.


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

626 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