Home » SQL & PL/SQL » SQL & PL/SQL » date format
date format [message #308258] Sat, 22 March 2008 22:41 Go to next message
horax
Messages: 34
Registered: March 2008
Member
If trying to select ALL dates within a range (Feb. 07, for example), and using the TO_CHAR statement for the formatting of the output, what is the proper syntax for selecting ALL dates in the range?

example:
SELECT idbasket, idshopper, TO_CHAR(dtordered 'Month dd, yyyy') AS "Date Ordered"
FROM bb_basket
WHERE dtordered='12-Feb-07';

HOWEVER, That would only bring up the dates for FEb 12...I need ALL days in Feb.

Is this posssible?
Re: date format [message #308260 is a reply to message #308258] Sat, 22 March 2008 22:49 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>Is this posssible?
Yes
by using
BETWEEN
or by using
AND

By the way when dealing with Oracle, STRING datatype are delimited by single quote marks.
'this 2008-03-22 is a string'
'23-Mar-2008' -- is a string
'March 23 2008' is a string, too
To convert strings to DATE datatypes use TO_DATE function

You should read & FOLLOW posting guidelines.
http://www.orafaq.com/forum/t/88153/0/

[Updated on: Sat, 22 March 2008 22:49] by Moderator

Report message to a moderator

Re: date format [message #308261 is a reply to message #308260] Sat, 22 March 2008 23:59 Go to previous messageGo to next message
horax
Messages: 34
Registered: March 2008
Member
Thank you. Sorry for the formatting issue, I just started on this board last night.

Here is what I came up with that seems to work:

SELECT IdBasket,
IdShopper,
To_char(dtordered,'Month dd, yyyy') AS "Date Ordered"
FROM bb_Basket
WHERE dtordered BETWEEN '31-Jan-07'
AND '29-Feb-07';



I hope this is more appropriate.


Thank you for your help!

hx

PS-The SQL formatter sucks! IT looks great on preview, but posting it looks terrible!

[Updated on: Sun, 23 March 2008 00:02]

Report message to a moderator

Re: date format [message #308262 is a reply to message #308258] Sun, 23 March 2008 00:11 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>PS-The SQL formatter sucks! IT looks great on preview, but posting it looks terrible!
PEBKAC!


You should read & FOLLOW posting guidelines.
http://www.orafaq.com/forum/t/88153/0/

>ROM bb_Basket WHERE dtordered BETWEEN '31-Jan-07' AND '29-Feb-07';

By the way when dealing with Oracle, STRING datatype are delimited by single quote marks.
'this 2008-03-22 is a string'
'23-Mar-2008' -- is a string
'March 23 2008' is a string, too
To convert strings to DATE datatypes use TO_DATE function


[Updated on: Sun, 23 March 2008 00:11] by Moderator

Report message to a moderator

Re: date format [message #308264 is a reply to message #308261] Sun, 23 March 2008 01:38 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
PS-The SQL formatter sucks! IT looks great on preview, but posting it looks terrible!

Put your query/code between [code] and [/code] tags as explained in the forum guide.

Regards
Michel
Previous Topic: Oracle dates
Next Topic: select statement options
Goto Forum:
  


Current Time: Sat Dec 03 18:00:17 CST 2016

Total time taken to generate the page: 0.09272 seconds