SYSDATE without seperators

zorrinn used Ask the Experts™

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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2004
use to_char()
try this:

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

It will give you output as string

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial