| Syntax to manipulate dates format [message #9138] |
Tue, 21 October 2003 14:57  |
Abiad
Messages: 9
Registered: October 2003
|
Junior Member |
|
|
Hi all,
This is a syntax Pb :
Let say we have a table T with BIRTHDAY (date format attribute), and I want to select BIRTHDAY from T where BIRTHDAY=Oct, 10 2003 or BIRTHDAY=Oct,11 2003
(there is several birthdays matching)
SQL> SELECT BIRTHDAY FROM T
WHERE to_char(BIRTHDAY, 'DD/MM/YYYY') in ('09/10/2003', '10/10/2003') ;
-- French format
Can anybody help me to write the correct request.
|
|
|
|
| Re: Syntax to manipulate dates format [message #9139 is a reply to message #9138] |
Tue, 21 October 2003 15:09  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You are close, but I would suggest converting your comparision values to dates instead of converting your column to a string.
select birthday
from t
where birthday in (to_date('09/10/2003', 'dd/mm/yyyy'), to_date('09/10/2003', 'dd/mm/yyyy')); |
|
|
|
| Re: Syntax to manipulate dates format [message #9148 is a reply to message #9139] |
Wed, 22 October 2003 06:15 |
Abiad
Messages: 9
Registered: October 2003
|
Junior Member |
|
|
Hi Todd,
I tried the suggestion, it's don't work
-- something wrong with this request :
SQL> SELECT BIRTHDAY FROM T
WHERE BIRTHDAY IN (TO_DATE('09/10/2003', 'dd/mm/yyyy'), TO_DATE('09/10/2003', 'dd/mm/yyyy'));
==> No rows selected
-- We have several fields matching Oct-09-2003 and oct-10-2003
SQL> SELECT TO_CHAR(BIRTHDAY, 'DD/MM/YYYY hh24:mi:ss') BIRTHDAY FROM T ;
==>
Name BIRTHDAY
------- ---------------
cathy 08/10/2003 02:19:06
solveur 09/10/2003 02:19:01
solveur 09/10/2003 02:19:04
fred 10/10/2003 02:19:04
fred 10/10/2003 02:19:07
fred 10/10/2003 02:19:08
--I want to SELECT BIRTHDAY where BIRTHDAY between (Oct 09-2003) and (Oct 10-2003) solver and fred in the sample
-- I tried this request but this is too complex, and -- I think there is other simple way
SQL> select to_char(birthday, 'DD/MM/YYYY hh24:mi:ss') from t
where BIRTHDAY between to_date ('09/10/2003 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
and to_date ('09/10/2003 23:59:59', 'dd/mm/yyyy hh24:mi:ss')
or
BIRTHDAY between to_date ('10/10/2003 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
and to_date ('10/10/2003 23:59:59', 'dd/mm/yyyy hh24:mi:ss') ;
???
|
|
|
|
| Re: Syntax to manipulate dates format [message #9157 is a reply to message #9148] |
Wed, 22 October 2003 12:09 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Well, you didn't mention that your date data contained TIME, did you?
select * from t
where birthday between to_date('09/10/2003', 'dd/mm/yyyy')
and to_date('10/10/2003', 'dd/mm/yyyy') + (1 - (1/24/60/60));
This will show all birthdays between 12:00am on the 9th to 11:59:59pm on the 10th.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
