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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle sql query 7 74
Oracle SQL Select unique values from two columns 4 66
dbms_crypto.decrypt   errors out 6 33
oracle collections 2 20
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

839 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