This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Trying to see if incoming date parameter has a time attached or not (wwithout using midnight) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Trying to see if incoming date parameter has a time attached or not (wwithout using midnight)" Watch "Trying to see if incoming date parameter has a time attached or not (wwithout using midnight)" New topic
Author

Trying to see if incoming date parameter has a time attached or not (wwithout using midnight)

Matt Kohanek
Village Idiot
Ranch Hand

Joined: Apr 04, 2009
Posts: 483

This is an interesting problem, but frustrating. I have an incoming DATE which can show up in either format:

TO_DATE('3/15/2012', 'MM/DD/YYYY')
TO_DATE('3/15/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')

I am getting this input in a parameter p_date (I have tried making that parameter a DATE, VARCHAR2 or TIMESTAMP, and have not had luck doing what I need yet)

I need to be able to tell if the calling app has specified the time or not. This is almost easy to do with something like this:



This works in all cases other than
TO_DATE('3/15/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
In this case, that IF will read as true, when the input time was actually specified

So can anyone suggest a way to accomplish this?


True wisdom is in knowing you know nothing - Socrates
Matt Kohanek
Village Idiot
Ranch Hand

Joined: Apr 04, 2009
Posts: 483

It might be more helpful if I specify the problem I am trying to deal with.

This date I am getting is an expiration date. If the date comes in like this:
TO_DATE('4/15/2012 14:22:00', 'MM/DD/YYYY HH24:MI:SS')

no problem, easy to set the exact expiration date and time.

However, if the date comes in like this:

TO_DATE('3/15/2012', 'MM/DD/YYYY')

I want to default the expiration date to the end of that day.

If I just use that date as is, it will use the time 00:00:00 which is the start of that day.

So if I convert this to a timestamp, I can check for time being set to midnight - this will usually mean the app did not specify time. But what about this:

TO_DATE('4/15/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')

In this case the app did specify the expire time, and that expire time is supposed to be the beginning of the day, not the end like I want to set it in all other cases.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Hi, it's me again

I'm not sure I understood your requirements, but if you can decide the type of the parameter, stick to DATE or TIMESTAMP. You could then use the TRUNC SQL function, which can operate on dates/timestamps and truncate it to a period you specify (look up the docs for details). You'd then compare the value of the parameter to the same parameter truncated to days -- if they matched, there was no time component (or the time component equaled exactly 0:00:00).

This of course means that the function would not be able to recognize situation time not specified from situation time specified and equals 0:00:00. That looks suspicious, but only you can know whether it poses a problem or not. Should not be the presence or absence of the time component specified by additional parameter?

Edit after your additional post: it seems to me that this situation would be best handled at the place the expiration date is being created/specified. Your function would always treated the date as if it had the time component. The situation where you want to expire at the end of given day would be handled by the caller of your function, who would simply add one day to his original date. This way it would seem more clear to me -- the date would be interpreted consistently by your function.
Matt Kohanek
Village Idiot
Ranch Hand

Joined: Apr 04, 2009
Posts: 483

Martin Vajsar wrote:Should not be the presence or absence of the time component be specified by additional parameter?


I am starting to lean this way myself - luckily this is all new code so I have some flexibility (add more params, such as BOOLEAN for whether time is indicated or not, or change the data type of the date param)
I was trying to make it so the calling app did not have to worry about this - I guess one way or the other the calling app is going to have to know and follow some type of rule for giving the date though.

I will post what I end up doing once I can get agreement from others :/

Thanks
Matt Kohanek
Village Idiot
Ranch Hand

Joined: Apr 04, 2009
Posts: 483

My project mgr agrees with me and we are just going to set the times to start of day rather than end of the timestamp is not specified, and indicate in the comments that this is the case - it is up to the consuming app to use the function correctly.
Thanks Martin
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

You're welcome.

I too think it is better this way.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Trying to see if incoming date parameter has a time attached or not (wwithout using midnight)
 
Similar Threads
Inserting date in database
java.sql.Timestamp to Oracle date
Oracle date-time fields
Resultset based on current Date and Time
oracle DATE