Home » SQL & PL/SQL » SQL & PL/SQL » Help with Datetime
Help with Datetime [message #294115] Wed, 16 January 2008 09:04 Go to next message
newbie22
Messages: 3
Registered: January 2008
Junior Member
Hi,

I have a stored procedure where I have declared three variables -
vSTART_DATE
vSTART_TIME
vEND_DATE

I want to compare these variables to a date field in my database. The database field is a DATE field that stores both date and time in the format 'DD-MON-YY HH24:MI:SS'. Comparison needs to be like this
vSTART_DATE >= START_DATETIME AND vEND_DATE <=START_DATETIME


There are two things that I am having trouble with and I am sure it's pretty easy.

1. I can just enter the vSTART_DATE value, say 16-JAN-08 and leave the vSTART_TIME blank in which case it should find all the records for 16-JAN-08 regardless of the time. Or I can enter both date and time, 16-JAN-08 14:23:00, where it will find records matching both date and time. How can I do this?

2. For vEND_DATE, I only want to check the date and set the time portion to 23:59:59 so that I get all records for that day.

So if I enter "16-JAN-08 14:23:00" for vSTART_DATE, vSTART_TIME and "26-JAN-08" for vEND_DATE, I want to see all the records between "16-JAN-08 14:23:00" and "26-JAN-08 23:59:59"

I hope it makes sense.

Thanks a lot in advacne!

Re: Help with Datetime [message #294120 is a reply to message #294115] Wed, 16 January 2008 09:27 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
All you need is the following functions

a) to_date -> convert the input into a date
b) nvl -> to check whether the given input is null
c) trunc -> to strip off the time component

Last but not least read the sql reference manual and do some test in your sql environment.

Regards

Raj
Re: Help with Datetime [message #294194 is a reply to message #294115] Wed, 16 January 2008 15:40 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
newbie22 wrote on Wed, 16 January 2008 10:04
The database field is a DATE field that stores both date and time in the format 'DD-MON-YY HH24:MI:SS'.



Just to be clear, DATEs are not stored in any format. They are output in a chosen format via a format mask or NLS_DATE_FORMAT.
Re: Help with Datetime [message #294231 is a reply to message #294115] Wed, 16 January 2008 22:25 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

 trunc -> to strip off the time component


I know Rajaram has adviced this one for the use of current scenario . Functionality of TRUNC on date depencs on its parameter.

This is newbie forum . So let me advice newbie22 (with your permission) to have a look at the TRUNC and Date Functions in detail for future use.

Thumbs Up
Rajuvan.

[Updated on: Wed, 16 January 2008 22:26]

Report message to a moderator

Previous Topic: Date Range disection - Can it be Done???
Next Topic: creating a query showing file not existing
Goto Forum:
  


Current Time: Mon Dec 05 15:19:05 CST 2016

Total time taken to generate the page: 0.07871 seconds