Home » SQL & PL/SQL » SQL & PL/SQL » Need a query to display the current date
Need a query to display the current date [message #233139] Wed, 25 April 2007 00:16 Go to next message
Maple50175
Messages: 16
Registered: April 2007
Location: MA
Junior Member

Hey all, I'm Brand new to SQL with Oracle and I am having alot of difficulties with it. I will be posting alot.

I need to write a query to display the current date. Label the column Date.

Date
--------------
10-JAN-02


Needs to look like this.
How do I type it out in SQL?

SQL> Select Date
from emp?
???

Let me know!

thank you
Re: Need a query to display the current date [message #233141 is a reply to message #233139] Wed, 25 April 2007 00:21 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

it's simple


SQL> select sysdate from dual;

SYSDATE
---------
25-APR-07
Re: Need a query to display the current date [message #233144 is a reply to message #233139] Wed, 25 April 2007 00:29 Go to previous messageGo to next message
Maple50175
Messages: 16
Registered: April 2007
Location: MA
Junior Member

Yes, but I need that date that is there.

10th of JAN 02.

and it's got to say DATE not sysdate.

If you put date in will it work? I dont have SQL on my laptop, only at work.. so i cant try it till the AM

[Updated on: Wed, 25 April 2007 00:31]

Report message to a moderator

Re: Need a query to display the current date [message #233149 is a reply to message #233139] Wed, 25 April 2007 00:41 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

I need to write a query to display the current date. Label the column Date.

You cannot have a column with a name DATE because date is a data type. But you can have the column with in double quotes. ie. "Date" but it is not advisable.

See the following error when you are using date as a column name

SQL> create table xyz ( id number, date date);
create table xyz ( id number, date date)
*
ERROR at line 1:
ORA-00904: : invalid identifier

But see the following modified one.

SQL> create table xyz ( id number, "Date" date);

Table created.

SQL> insert into xyz values (1,'10-JAN-02');

1 row created.

SQL> select "Date" from xyz;

Date
---------
10-JAN-02

Re: Need a query to display the current date [message #233153 is a reply to message #233149] Wed, 25 April 2007 00:53 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
You can use an alias for a selected column.
Do NOT use double quotes for your columnnames!!
SQL> create table faq(mydate date);

Table created.

SQL> insert into faq values (to_date('10-jan-2002', 'dd-mon-yyyy'));

1 row created.

SQL> select mydate as "Date"
  2  from   faq;

Date
---------
10-JAN-02


Quote:
SQL> insert into xyz values (1,'10-JAN-02');

This is an error. The second column has datatype date, not varchar2.
Re: Need a query to display the current date [message #233157 is a reply to message #233139] Wed, 25 April 2007 01:05 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

Quote:
This is an error. The second column has datatype date, not varchar2.



This may not be an error. Because oracle accepts string date which is in default format(dd-mon-yy) automatically. But we have to aware of two digited year and the behavior of manipulation of dates in Oracle.


Thanx,
Ramesh
Re: Need a query to display the current date [message #233160 is a reply to message #233157] Wed, 25 April 2007 01:13 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
SQL> alter session set nls_date_format='DD-MM-YYYY';

Session altered.

SQL> ALTER session set nls_date_language='ITALIAN';

Session altered.

SQL> insert into faq values ('01-JAN-02');
insert into faq values ('01-JAN-02')
                        *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

This IS an error. It is not independent of your settings.
Get used to always using explicit to_date/to_char/to_number
Re: Need a query to display the current date [message #233164 is a reply to message #233157] Wed, 25 April 2007 01:20 Go to previous messageGo to next message
Maaher
Messages: 7041
Registered: December 2001
Senior Member
rameshuddaraju wrote on Wed, 25 April 2007 08:05
This may not be an error. Because oracle accepts string date which is in default format(dd-mon-yy) automatically.
It IS an error. Oracle is kind enough to try to do the casting from VARCHAR2 to DATE for you. Yes, it will pass in a lot of cases but it is a very bad habit that will cause problems sooner or later. It is not because Oracle lets it slide that it is correct. Implicit casting is giving control away. As a programmer, I'd like to keep control over my data.

MHE

Edit: didn't see your reply, Frank. Strange, we seem to agree. Very Happy

[Updated on: Wed, 25 April 2007 01:22]

Report message to a moderator

Re: Need a query to display the current date [message #233278 is a reply to message #233139] Wed, 25 April 2007 08:45 Go to previous message
Bill B
Messages: 1066
Registered: December 2004
Senior Member



select to_char(sysdate,'DD-MON-YY') "Date" from dual;
Previous Topic: Sending secure/ encrypted mail from PL/SQL
Next Topic: Identifier Too Long
Goto Forum:
  


Current Time: Tue Jul 22 04:24:06 CDT 2014

Total time taken to generate the page: 0.24029 seconds