Re: Help : Converting Dates to Chars

From: Mark D Powell <markp7832_at_my-deja.com>
Date: Mon, 18 Sep 2000 17:54:23 GMT
Message-ID: <8q5ks3$v8n$1@nnrp1.deja.com>

In article <39C5F6BF.689370EC_at_NOSPAMramboll-it.dk>,   mhr_at_NOSPAMramboll-it.dk wrote:
> Hi,
>
> One way is to change the default date format in order to produce the
> formatting of dates that you want. This could be done within your
 session
> and thereby avoiding influencing other users/applications conencted
 to the
> database. In SQL*PLUS the default date format is change with for
 example:
>
> (c:\tmp) SQL> alter session set nls_date_format = 'dd-mm-yyyy
 hh24:mi:ss';
>
> Session altered.
>
> (c:\tmp) SQL> select sysdate from dual;
>
> SYSDATE
> -------------------
> 18-09-2000 13:15:20
>
> (c:\tmp) SQL>
>
> Hope this helps!
>
> rezaferry_at_my-deja.com wrote:
>
> > I have an SQL statement which goes
> > select * from <table>
> > I use this same statement to get the data from each table. The
 problem
> > is when I encounter a Date field I only get the date and not the
 time,
> > while I need both the date and the time data.
> >
> > Eg : If I have a data 9-01-2000, 12:50 it only shows 9-01-2000. How
 do
> > I change the settings so that oracle automatically shows 9-01-2000.
> > 12:50.
> >
> > And since I use it on different tables, I have no efficient way of
> > knowing the name and the types of each field before hand. Thus I
 cannot
> > just use to_char (D_SOMETHING, 'DD-MM.......').
> >
> > Thanks ....
> >

While setting the session default date format to include the time may be a valid option I think just using the to_char function to format the date as you want it is more practical since other SQL that is expecting date information in the normal default format will not be affected.

select to_char(date_col,'format_string') from ....

The to_char function and the date format string are documented in the SQL Manual.

OPS2> select to_char(sysdate,'YYYYMMDD HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'

---

20000918 13:55:01

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Mon Sep 18 2000 - 12:54:23 CDT