zorrinn
asked on
SYSDATE without seperators
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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',' yyyymmddhh 24miss')
/
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)
insert into my_dates (date_column) values (sysdate);
select * from my_dates
where date_column = to_char('20031026080030','
/
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 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', 'yyyymmddh hmiss'));
select * from date_insert_test where to_char(test_date,'YYYY') = '1985';
drop table date_insert_test;
Hope this helps.
create table date_insert_test (test_date date);
insert into date_insert_test (test_date) values (to_date('19850215123015',
select * from date_insert_test where to_char(test_date,'YYYY') = '1985';
drop table date_insert_test;
Hope this helps.
select to_char(sysdate,'yyyymmddh
It will give you output as string
20031028111026