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

[Matt Kohanek]

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:

p_date TIMESTAMP) IS
...
BEGIN
IF substr(p_date, 11) = '12.00.00.000000 AM'  THEN
   --time not specified
ELSE
   --time specified
END IF;

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?

[Matt Kohanek]

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 Vashko]

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]

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]

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 Vashko]

You're welcome.

I too think it is better this way.