Home » SQL & PL/SQL » SQL & PL/SQL » Questions about to_char and Assigning date to a DATE datatype variable
Questions about to_char and Assigning date to a DATE datatype variable [message #305081] Sat, 08 March 2008 04:36 Go to next message
oraclewannabe
Messages: 26
Registered: January 2008
Junior Member
Hi, everyone. can i know if

i wish to assigned a date to a date variable which includes hour,minutes and seconds do i have to use a to_date function everytime ?
like this

1) v_date DATE = to_date('25-02-2008 09:25:23', 'DD-MM-YYYY HH24:MI:SS')
is there any simpler method ?

2) v_date NUMBER = to_number(to_char(trunc(v_date),'D'));
dbms_output.put_line(v_date);

Can i know why do i always get a number 2 when 25-02-2008 is a MONDAY ?

do i have some misunderstanding here?
any advice pls by gurus ?
Re: Questions about to_char and Assigning date to a DATE datatype variable [message #305082 is a reply to message #305081] Sat, 08 March 2008 04:41 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
do i have to use a to_date function everytime ?
Unless the value being used is already a date (for example, passing a date variable or a function that returns a date - such as SYSDATE)
Quote:
Can i know why do i always get a number 2 when 25-02-2008 is a MONDAY ?

What gets rety=urned for a Sunday, what about a Wednesday or a Friday. Can you work this out yourself now?
Re: Questions about to_char and Assigning date to a DATE datatype variable [message #305084 is a reply to message #305081] Sat, 08 March 2008 05:05 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
1) use this way if you do not want to get into trouble (ab)using and relying on implicit conversion
This is the recommended and correct way

2) SQL Reference
Chapter 2 Basic Elements of Oracle SQL
Format Models
Datetime Format Models
Datetime Format Elements and Globalization Support
Quote:
The datetime format element D returns the number of the day of the week (1-7). The day of the week that is numbered 1 is specified implicitly by the initialization parameter NLS_TERRITORY.


[Edit: Changed link to 9i documentation]

[Updated on: Sat, 08 March 2008 05:48]

Report message to a moderator

Previous Topic: Order of SQL execution
Next Topic: Procedure in ksh
Goto Forum:
  


Current Time: Sat Dec 10 14:47:24 CST 2016

Total time taken to generate the page: 0.09814 seconds