Hello
I have a pass through query from access to oracle, where is such
condition: REGISTER_EVENT_DETAILS_SLOTS.UPDATED_DATE >= '21-JAN-2010'

What is date format in Oracle SQL, if I want to do not only date, but
hours and minutes too?

Thank you.

"Galka" <galkas@mail.ru> wrote in message 
news:46619abc-99e7-4916-83ca-b343b8eb35a5@r5g2000yqb.googlegroups.com...
> Hello
> I have a pass through query from access to oracle, where is such
> condition: REGISTER_EVENT_DETAILS_SLOTS.UPDATED_DATE >= '21-JAN-2010'
>
> What is date format in Oracle SQL, if I want to do not only date, but
> hours and minutes too?
>
> Thank you.

The following article may help you.  Regards  Richard Cooke

Oracle Dates and Times
--------------------------------------------------------------------------------

  a.. Overview
  b.. DATE Format
  c.. The Current Time
  d.. Operations on DATE
  e.. Further Information

--------------------------------------------------------------------------------
Overview
Oracle supports both date and time, albeit differently from the SQL2 
standard. Rather than using two separate entities, date and time, Oracle 
only uses one, DATE. The DATE type is stored in a special internal format 
that includes not just the month, day, and year, but also
the hour, minute, and second.
The DATE type is used in the same way as other built-in types such as INT. 
For example, the following SQL statement creates a relation with an 
attribute of type DATE:

create table x(a int, b date);
--------------------------------------------------------------------------------
DATE Format
When a DATE value is displayed, Oracle must first convert that value from 
the special internal format to a printable string. The conversion is done by 
a function TO_CHAR, according to a DATE format. Oracle's default format for 
DATE is "DD-MON-YY". Therefore, when you issue the query
select b from x;you will see something like:
B
---------
01-APR-98Whenever a DATE value is displayed, Oracle will call TO_CHAR 
automatically with the default DATE format. However, you may override the 
default behavior by calling TO_CHAR explicitly with your own DATE format. 
For example,
SELECT TO_CHAR(b, 'YYYY/MM/DD') AS b
FROM x;returns the result:
B
---------------------------------------------------------------------------
1998/04/01The general usage of TO_CHAR is:
TO_CHAR(<date>, '<format>')where the <format> string can be formed from over 
40 options. Some of the more popular ones include:
  MM Numeric month (e.g., 07)
      MON Abbreviated month name (e.g., JUL)
      MONTH Full month name (e.g., JULY)
      DD Day of month (e.g., 24)
      DY Abbreviated name of day (e.g., FRI)
      YYYY 4-digit year (e.g., 1998)
      YY Last 2 digits of the year (e.g., 98)
      RR Like YY, but the two digits are ``rounded'' to a year in the range 
1950 to 2049. Thus, 06 is considered 2006 instead of 1906 , for example.
      AM (or PM) Meridian indicator
      HH Hour of day (1-12)
      HH24 Hour of day (0-23)
      MI Minute (0-59)
      SS Second (0-59)

You have just learned how to output a DATE value using TO_CHAR. Now what 
about inputting a DATE value? This is done through a function called 
TO_DATE, which converts a string to a DATE value, again according to the 
DATE format. Normally, you do not have to call TO_DATE explicitly: Whenever 
Oracle expects a DATE value, it will automatically convert your input string 
using TO_DATE according to the default DATE format "DD-MON-YY". For example, 
to insert a tuple with a DATE attribute, you can simply type:

insert into x values(99, '31-may-98');Alternatively, you may use TO_DATE 
explicitly:
insert into x
values(99, to_date('1998/05/31:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));The 
general usage of TO_DATE is:
TO_DATE(<string>, '<format>')where the <format> string has the same options 
as in TO_CHAR.
Finally, you can change the default DATE format of Oracle from "DD-MON-YY" 
to something you like by issuing the following command in sqlplus:

alter session set NLS_DATE_FORMAT='<my_format>';The change is only valid for 
the current sqlplus session.

--------------------------------------------------------------------------------
The Current Time
The built-in function SYSDATE returns a DATE value containing the current 
date and time on your system. For example,
select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Time"
from dual;returns
Current Time
---------------------------------------------------------------------------
Tue 21-Apr-1998 21:18:27which is the time when I was preparing this document 
:-) Two interesting things to note here:
  a.. You can use double quotes to make names case sensitive (by default, 
SQL is case insensitive), or to force spaces into names. Oracle will treat 
everything inside the double quotes literally as a single name. In this 
example, if "Current Time" is not quoted, it would have been interpreted as 
two case insensitive names CURRENT and TIME, which would actually cause a 
syntax error.
  b.. DUAL is built-in relation in Oracle which serves as a dummy relation 
to put in the FROM clause when nothing else is appropriate. For example, try 
"select 1+2 from dual;".
Another name for the built-in function SYSDATE is CURRENT_DATE. Be aware of 
these special names to avoid name conflicts.

--------------------------------------------------------------------------------
Operations on DATE
You can compare DATE values using the standard comparison operators such as 
=, !=, >, etc.
You can subtract two DATE values, and the result is a FLOAT which is the 
number of days between the two DATE values. In general, the result may 
contain a fraction because DATE also has a time component. For obvious 
reasons, adding, multiplying, and dividing two DATE values are not allowed.

You can add and subtract constants to and from a DATE value, and these 
numbers will be interpreted as numbers of days. For example, SYSDATE+1 will 
be tomorrow. You cannot multiply or divide DATE values.

With the help of TO_CHAR, string operations can be used on DATE values as 
well. For example, to_char(<date>, 'DD-MON-YY') like '%JUN%' evaluates to 
true if <date> is in June.


--------------------------------------------------------------------------------
This document was written originally by Kristian Widjaja for Prof. Jeff 
Ullman's CS145 class in Autumn, 1997; revised by Jun Yang for Prof. Jennifer 
Widom's CS145 class in Spring, 1998; further revisions by Prof. Ullman in 
Autumn, 1998. 

On Jan 21, 9:50=A0am, "Richard Cooke" <richard.co...@baesystems.com>
wrote:
> "Galka" <gal...@mail.ru> wrote in message
>
> news:46619abc-99e7-4916-83ca-b343b8eb35a5@r5g2000yqb.googlegroups.com...
>
> > Hello
> > I have a pass through query from access to oracle, where is such
> > condition: REGISTER_EVENT_DETAILS_SLOTS.UPDATED_DATE >=3D '21-JAN-2010'
>
> > What is date format in Oracle SQL, if I want to do not only date, but
> > hours and minutes too?
>
> > Thank you.
>
> The following article may help you. =A0Regards =A0Richard Cooke
>
> Oracle Dates and Times
> -------------------------------------------------------------------------=
--=AD-----
>
> =A0 a.. Overview
> =A0 b.. DATE Format
> =A0 c.. The Current Time
> =A0 d.. Operations on DATE
> =A0 e.. Further Information
>
> -------------------------------------------------------------------------=
--=AD-----
> Overview
> Oracle supports both date and time, albeit differently from the SQL2
> standard. Rather than using two separate entities, date and time, Oracle
> only uses one, DATE. The DATE type is stored in a special internal format
> that includes not just the month, day, and year, but also
> the hour, minute, and second.
> The DATE type is used in the same way as other built-in types such as INT=
..
> For example, the following SQL statement creates a relation with an
> attribute of type DATE:
>
> create table x(a int, b date);
> -------------------------------------------------------------------------=
--=AD-----
> DATE Format
> When a DATE value is displayed, Oracle must first convert that value from
> the special internal format to a printable string. The conversion is done=
 by
> a function TO_CHAR, according to a DATE format. Oracle's default format f=
or
> DATE is "DD-MON-YY". Therefore, when you issue the query
> select b from x;you will see something like:
> B
> ---------
> 01-APR-98Whenever a DATE value is displayed, Oracle will call TO_CHAR
> automatically with the default DATE format. However, you may override the
> default behavior by calling TO_CHAR explicitly with your own DATE format.
> For example,
> SELECT TO_CHAR(b, 'YYYY/MM/DD') AS b
> FROM x;returns the result:
> B
> -------------------------------------------------------------------------=
--
> 1998/04/01The general usage of TO_CHAR is:
> TO_CHAR(<date>, '<format>')where the <format> string can be formed from o=
ver
> 40 options. Some of the more popular ones include:
> =A0 MM Numeric month (e.g., 07)
> =A0 =A0 =A0 MON Abbreviated month name (e.g., JUL)
> =A0 =A0 =A0 MONTH Full month name (e.g., JULY)
> =A0 =A0 =A0 DD Day of month (e.g., 24)
> =A0 =A0 =A0 DY Abbreviated name of day (e.g., FRI)
> =A0 =A0 =A0 YYYY 4-digit year (e.g., 1998)
> =A0 =A0 =A0 YY Last 2 digits of the year (e.g., 98)
> =A0 =A0 =A0 RR Like YY, but the two digits are ``rounded'' to a year in t=
he range
> 1950 to 2049. Thus, 06 is considered 2006 instead of 1906 , for example.
> =A0 =A0 =A0 AM (or PM) Meridian indicator
> =A0 =A0 =A0 HH Hour of day (1-12)
> =A0 =A0 =A0 HH24 Hour of day (0-23)
> =A0 =A0 =A0 MI Minute (0-59)
> =A0 =A0 =A0 SS Second (0-59)
>
> You have just learned how to output a DATE value using TO_CHAR. Now what
> about inputting a DATE value? This is done through a function called
> TO_DATE, which converts a string to a DATE value, again according to the
> DATE format. Normally, you do not have to call TO_DATE explicitly: Whenev=
er
> Oracle expects a DATE value, it will automatically convert your input str=
ing
> using TO_DATE according to the default DATE format "DD-MON-YY". For examp=
le,
> to insert a tuple with a DATE attribute, you can simply type:
>
> insert into x values(99, '31-may-98');Alternatively, you may use TO_DATE
> explicitly:
> insert into x
> values(99, to_date('1998/05/31:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));The
> general usage of TO_DATE is:
> TO_DATE(<string>, '<format>')where the <format> string has the same optio=
ns
> as in TO_CHAR.
> Finally, you can change the default DATE format of Oracle from "DD-MON-YY=
"
> to something you like by issuing the following command in sqlplus:
>
> alter session set NLS_DATE_FORMAT=3D'<my_format>';The change is only vali=
d for
> the current sqlplus session.
>
> -------------------------------------------------------------------------=
--=AD-----
> The Current Time
> The built-in function SYSDATE returns a DATE value containing the current
> date and time on your system. For example,
> select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Time"
> from dual;returns
> Current Time
> -------------------------------------------------------------------------=
--
> Tue 21-Apr-1998 21:18:27which is the time when I was preparing this docum=
ent
> :-) Two interesting things to note here:
> =A0 a.. You can use double quotes to make names case sensitive (by defaul=
t,
> SQL is case insensitive), or to force spaces into names. Oracle will trea=
t
> everything inside the double quotes literally as a single name. In this
> example, if "Current Time" is not quoted, it would have been interpreted =
as
> two case insensitive names CURRENT and TIME, which would actually cause a
> syntax error.
> =A0 b.. DUAL is built-in relation in Oracle which serves as a dummy relat=
ion
> to put in the FROM clause when nothing else is appropriate. For example, =
try
> "select 1+2 from dual;".
> Another name for the built-in function SYSDATE is CURRENT_DATE. Be aware =
of
> these special names to avoid name conflicts.
>
> -------------------------------------------------------------------------=
--=AD-----
> Operations on DATE
> You can compare DATE values using the standard comparison operators such =
as
> =3D, !=3D, >, etc.
> You can subtract two DATE values, and the result is a FLOAT which is the
> number of days between the two DATE values. In general, the result may
> contain a fraction because DATE also has a time component. For obvious
> reasons, adding, multiplying, and dividing two DATE values are not allowe=
d.
>
> You can add and subtract constants to and from a DATE value, and these
> numbers will be interpreted as numbers of days. For example, SYSDATE+1 wi=
ll
> be tomorrow. You cannot multiply or divide DATE values.
>
> With the help of TO_CHAR, string operations can be used on DATE values as
> well. For example, to_char(<date>, 'DD-MON-YY') like '%JUN%' evaluates to
> true if <date> is in June.
>
> -------------------------------------------------------------------------=
--=AD-----
> This document was written originally by Kristian Widjaja for Prof. Jeff
> Ullman's CS145 class in Autumn, 1997; revised by Jun Yang for Prof. Jenni=
fer
> Widom's CS145 class in Spring, 1998; further revisions by Prof. Ullman in
> Autumn, 1998.

Richard
Thank you. TO_DATE function does work.
Galina