Home » SQL & PL/SQL » SQL & PL/SQL » selecting date interval
selecting date interval [message #289252] Thu, 20 December 2007 07:46 Go to next message
tomukaz
Messages: 16
Registered: December 2007
Junior Member
Hi,

heres a newbie question: I have a table field haha, witch type is DATE.
Howto select a date interval from 2007.01.01 to 2007.06.30 ?
Re: selecting date interval [message #289254 is a reply to message #289252] Thu, 20 December 2007 07:49 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Use BETWEEN clause.

Kiran.
Re: selecting date interval [message #289261 is a reply to message #289252] Thu, 20 December 2007 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And TO_DATE function.

Regards
Michel
Re: selecting date interval [message #289264 is a reply to message #289252] Thu, 20 December 2007 08:18 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Of course TO_DATE.

Quote:

I have a table field haha, witch type is DATE.

But i didn't understand about 2007.01.01 to 2007.06.30 date format. How the DATE data type field will take this value?

Kiran.

[Updated on: Thu, 20 December 2007 08:19]

Report message to a moderator

Re: selecting date interval [message #289265 is a reply to message #289252] Thu, 20 December 2007 08:25 Go to previous messageGo to next message
tomukaz
Messages: 16
Registered: December 2007
Junior Member
I need to calculate some daba between 2007.01.01 to 2007.06.30

select pa1_gal_gist_id||';'||pa1_gist_pav||';'||pa1_pas_tipo_aprasymas||';'||pa1_pas_kodas||';'||pa1_pas_profilis||';'||sum(pa1_apmokamu_pas laugu_sk) from paslaugu_ataskaitoms_1 where pa1_ataskaitinis_laikotarpis = between '2007.01.01' and '2007.11.30'
and pa1_gal_gist_id in (449,448,447,460,459)
group by pa1_gal_gist_id, pa1_gist_pav, pa1_pas_tipo_aprasymas, pa1_pas_kodas, pa1_pas_profilis, pa1_apmokamu_paslaugu_sk
order by pa1_pas_tipo_aprasymas, pa1_gist_pav
;
Re: selecting date interval [message #289266 is a reply to message #289265] Thu, 20 December 2007 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where is TO_DATE function?
BETWEEN

Regards
Michel

[Updated on: Thu, 20 December 2007 08:34]

Report message to a moderator

Re: selecting date interval [message #289268 is a reply to message #289252] Thu, 20 December 2007 08:34 Go to previous messageGo to next message
tomukaz
Messages: 16
Registered: December 2007
Junior Member
You mean, it should look like:

pa1_ataskaitinis_laikotarpis = between to_date('2007.01.01', 'YYYY.MM.DD') and to_date( '2007.06.30', 'YYYY.MM.DD')

?
Re: selecting date interval [message #289269 is a reply to message #289252] Thu, 20 December 2007 08:35 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

where pa1_ataskaitinis_laikotarpis = between '2007.01.01' and '2007.11.30'


Even syntax is wrong. Check it properly.


SQL> SELECT *
  2    FROM GSM_SERVICE_MAST
  3   WHERE REGISTRATION_DATE_D =  BETWEEN TO_DATE ('01012001', 'ddmmyyyy') AND TO_DATE ('01012008',
 'ddmmyyyy');
 WHERE REGISTRATION_DATE_D =  BETWEEN TO_DATE ('01012001', 'ddmmyyyy') AND TO_DATE ('01012008', 'ddm
                              *
ERROR at line 3:
ORA-00936: missing expression
Re: selecting date interval [message #289270 is a reply to message #289268] Thu, 20 December 2007 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

pa1_ataskaitinis_laikotarpis = between to_date('2007.01.01', 'YYYY.MM.DD') and to_date( '2007.06.30', 'YYYY.MM.DD')

This is a first step, the second one is on syntax of use of BETWEEN as shown by Kiran.

Regards
Michel
Re: selecting date interval [message #289275 is a reply to message #289252] Thu, 20 December 2007 08:49 Go to previous messageGo to next message
tomukaz
Messages: 16
Registered: December 2007
Junior Member
Well it doens't show any error for now...

Maby it would be better, this looked like this:

pa1_ataskaitinis_laikotarpis = ( trunc (pa1_ataskaitinis_laikotarpis ) >= to_date('2007.01.01') AND trunc(pa1_ataskaitinis_laikotarpis ) < to_date
('2007.06.30') )

?
Re: selecting date interval [message #289277 is a reply to message #289275] Thu, 20 December 2007 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not?
The meaning is not the same but only YOU know if with your data it is the same.

Regards
Michel
Re: selecting date interval [message #289278 is a reply to message #289252] Thu, 20 December 2007 09:03 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

 to_date('2007.01.01') 


huh?

Maby it would be better, you go back and read TO_DATE function once and also BETWEEN.

Kiran.
Re: selecting date interval [message #289439 is a reply to message #289252] Fri, 21 December 2007 12:31 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Read up on using the between command.

pa1_ataskaitinis_laikotarpis between to_date('2007.01.01', 'YYYY.MM.DD') and to_date( '2007.06.30', 'YYYY.MM.DD')
Previous Topic: please give me solution for this program
Next Topic: Sequence Name
Goto Forum:
  


Current Time: Tue Dec 06 06:10:48 CST 2016

Total time taken to generate the page: 0.05037 seconds