Home » SQL & PL/SQL » SQL & PL/SQL » dates
dates [message #236021] Tue, 08 May 2007 13:12 Go to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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
or
TRUNC(pdtFromDate) + INTERVAL '16' HOURS

To know its meaninig, search for the mentioned functions in SQL Reference.
Re: dates [message #236041 is a reply to message #236021] Tue, 08 May 2007 15:15 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
thank you Smile
Re: dates [message #236088 is a reply to message #236041] Wed, 09 May 2007 00:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't use "date" as a column name or variable name. It is a reserved word.
Re: dates [message #236414 is a reply to message #236021] Wed, 09 May 2007 23:38 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: Using COBOL to acess a Oracle databse
Next Topic: Write a single query to print calender
Goto Forum:
  


Current Time: Tue Dec 06 13:52:14 CST 2016

Total time taken to generate the page: 0.17904 seconds