| dates [message #236021] |
Tue, 08 May 2007 13:12  |
yog_23
Messages: 79
Registered: March 2007
|
Member |
|
|
I have 2 params in a proc. (DB field : DATE datatype)
Need to reset the time to 4:00 PM for these dates and then compare against a column.
Is this the way to do this ? Any better way ?
/**Declaration**/
DECLARE
ldtStartDate DATE := to_date(pdtFromDate,'MM/DD/YYYY') || ' 16:00:00';
ldtEndDate DATE; := to_date(pdtEndDate,'MM/DD/YYYY') || ' 16:00:00';
SELECT *
FROM
tablea
WHERE
CREATE_DATE >= to_date(ldtStartDate,'MM/DD/YYYY HH24:MM:SS') and
CREATION_DATE < to_date(ldtEndDate,'MM/DD/YYYY HH24:MM:SS');
Is the to_date and format needed for the DB column too ?
|
|
|
|
| Re: dates [message #236027 is a reply to message #236021] |
Tue, 08 May 2007 13:43  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Function TO_DATE converts VARCHAR2 to DATE; NEVER use it with DATE parameter.
If pdtFromDate is DATE, and you want to get the date with the same day at 4:00 pm, use TRUNC(pdtFromDate) + 16/24 TRUNC(pdtFromDate) + INTERVAL '16' HOURS
To know its meaninig, search for the mentioned functions in SQL Reference.
|
|
|
|
|
|
|
|
| Re: dates [message #236414 is a reply to message #236021] |
Wed, 09 May 2007 23:38 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Frank: what are you pointing at? Although I used the word DATE, it was always in the meaning of the DATE datatype, as yog_23 used variables with proper names.
yog_23: just for completeness, your local variables have DATE datatype. so, according to my first note, you should remove TO_DATE conversions from the WHERE clause (hope you did it already).
|
|
|
|
| Re: dates [message #236518 is a reply to message #236414] |
Thu, 10 May 2007 02:47 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
hm, must have missed my coffee, as a respected member here would say.
I thought in the OP a variable called DATE was used...
Sorry.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
