Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SYSDATE without seperators

Posted on 2003-10-28
6
Medium Priority
?
1,562 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
[X]
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
6 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 80 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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

722 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