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.
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 :/
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.