Home » SQL & PL/SQL » SQL & PL/SQL » How to deal with DATE format?????????
How to deal with DATE format????????? [message #186326] Mon, 07 August 2006 08:54 Go to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
Please check the attached file.

Why doesn't the query "select * from emp where hiredate = '28-JUL-06';" display record with empno = 1111?
  • Attachment: DATE.txt
    (Size: 6.97KB, Downloaded 168 times)
Re: How to deal with DATE format????????? [message #186328 is a reply to message #186326] Mon, 07 August 2006 09:12 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
1. You compare a date to a varchar2. What you should do is cast the varchar2 to a date:
SELECT * 
FROM   emp
WHERE  hiredate = to_date('28-JUL-2006','DD-MON-YYYY');


2. A date contains a time component. This defaults to 00:00:00 but it is very well possible that the record with empno 1111 has a time different from 00:00:00. This will cause the where clause to evaluate to FALSE and to skip the record. What happens if you truncate the hiredate?

MHE
Re: How to deal with DATE format????????? [message #186335 is a reply to message #186326] Mon, 07 August 2006 09:35 Go to previous messageGo to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
You are right Sir...below is the output

Please check one more question at the bottom.

===============================

SQL> select * from emp where hiredate = '28-JUL-06';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
4444 xyz job - 28-JUL-06 2000 - 10
5555 abcdef job123 - 28-JUL-06 5555 - 20
2222 xyz abc - 28-JUL-06 1000 - 10
3333 xyz job - 28-JUL-06 2000 - 10

SQL> select * from emp where trunc(hiredate) = '28-JUL-06';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
4444 xyz job - 28-JUL-06 2000 - 10
5555 abcdef job123 - 28-JUL-06 5555 - 20
2222 xyz abc - 28-JUL-06 1000 - 10
3333 xyz job - 28-JUL-06 2000 - 10
1111 aaa job 7782 28-JUL-06 1000 - 10

==============================

Now one more question related to this:

When we pass DATE IN variable to PL/SQL proc, what is recommonded:

1. Pass it as VARCHAR2 and convert to required format before using in DML statements inside proc.

2. Pass it as DATE. Be consistent with DATE format. Change NLS_DATE_FORMAT in init.ora to some standard format as per requirement and use that format throughout all the applications.

Thanks for the quick reply and expect the same Smile
Re: How to deal with DATE format????????? [message #186338 is a reply to message #186335] Mon, 07 August 2006 09:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Option 2 every time.
You don't need one single date format across everything - as long as you pass dates in DATE datatypes, the only thing the NLS_DATE_FORMAT affects is what happens when you do a TO_CHAR or TO_DATE without specify the date format.
Re: How to deal with DATE format????????? [message #186339 is a reply to message #186338] Mon, 07 August 2006 09:50 Go to previous messageGo to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
Can you clarify please? If I pass IN param as DATE how does it is recognized against the particular date format for the session?
Re: How to deal with DATE format????????? [message #186340 is a reply to message #186339] Mon, 07 August 2006 09:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you pass in a parameter as a DATE, and compare it against other DATE variables or columns, then it does not matter what the NLS_DATE_FORMAT is set to.

All DATEs are DATEs. There is no format held with a date parameter - it's just recorded as the number of seconds after an arbitrary point in time.
Re: How to deal with DATE format????????? [message #186342 is a reply to message #186326] Mon, 07 August 2006 10:07 Go to previous messageGo to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
So if we have IN param as DATE then we can pass it in any format and still it can be compared correctly against any DATE variable or DATE column inside proc. Am I right?
Re: How to deal with DATE format????????? [message #186344 is a reply to message #186342] Mon, 07 August 2006 10:18 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Just to help clarify, don't get formats and datatypes confused

'19-Jan-2006' is NOT a date, it is text / character / strring (whatever you want to call it)

to_date('19-Jan-2006') will convert the string into a date
BUT ONLY if the NLS_DATE_FORMAT is set to dd-mon-yyyy.
If it is set to, say. 'mm/d/yyyy' then to_date('19-Jan-2006') will give an error.
you should always use the second argument of the to_date function.
Re: How to deal with DATE format????????? [message #186353 is a reply to message #186326] Mon, 07 August 2006 11:27 Go to previous messageGo to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
okay, that means declare IN param as DATE and while passing it to proc use TO_DATE with appropriate format.

Example:

procedure MyProc(MyDate IN DATE) as
v_MyDate DATE;
begin
select hiredate
into v_MyDate
from emp
where hiredate = MyDate;
end;

Now call this proc as below:

1. MyProc(to_date('23-NOV-71', 'DD-MON-YY'))

2. MyProc(to_date('11/23/1971', 'MM/DD/YYYY'))

3. MyProc(to_date('11-23-1971', 'MM-DD-YYYY'))

Am I on right path???

Now, one last question, which century format is preferred, 'YYYY' or 'RRRR'???

Thanks guys for your quick responses....cheers!!!


Re: How to deal with DATE format????????? [message #186452 is a reply to message #186353] Tue, 08 August 2006 00:59 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Yes, you are on the right track. As for the date formats 'RRRR' and 'YYYY', RRRR is the 'round year'. It accepts a 4 digit year input and a 2 digit year input. If you provide only 2 digits Oracle will apply the same rules as for the 'RR' year format. Details can be found in the SQL reference. The link might require a free OTN subscription. So if you provide 4 digits for the year, it makes no difference. The RRRR format is often used in Forms because it is more lenient to the end user.

MHE
Re: How to deal with DATE format????????? [message #186491 is a reply to message #186326] Tue, 08 August 2006 03:12 Go to previous message
ramesh_samane
Messages: 63
Registered: May 2006
Member
Thanks buddy for the reply...this is really awesome site...you guys are doing a great job...keep it up!!! Cheers!!!
Previous Topic: DBMS_OBFUSCATION_TOOLKIT Package
Next Topic: LIKE with variable
Goto Forum:
  


Current Time: Tue Dec 06 12:23:35 CST 2016

Total time taken to generate the page: 0.12090 seconds