comparing date in form 6i [message #518307] |
Wed, 03 August 2011 01:18 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/9fc2c55773e60a8cef395fa8cb47a31f?s=64&d=mm&r=g) |
goparaju
Messages: 54 Registered: June 2011
|
Member |
|
|
Hi Experts,
I am working in form 6i, database 9i. I have datablock on table t1.
table t1: name(varchar2), date(varchar2)
datablock: name(varchar2), date(varchar2)[i have insert date with
time stamp]
for date column, i am inserting date with time stamp.
Requirement: While querying data, user just enters only date(no time stamp), i should be able to query data.
I tried in data block where condition
WHERE: substr(date,1,11)=nvl(:block.date,substr(date,1,11)
i am not able to retrieve data through date, through name it's working fine.
Could somebody help me how to retrieve data by only entering date (without time stamp)
Thanks in advance.
|
|
|
|
|
|
|
|
Re: comparing date in form 6i [message #518363 is a reply to message #518355] |
Wed, 03 August 2011 07:17 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/9fc2c55773e60a8cef395fa8cb47a31f?s=64&d=mm&r=g) |
goparaju
Messages: 54 Registered: June 2011
|
Member |
|
|
Hi,
Thanks for your reply. But when u inserted data in your example, u inserted only date(without time stamp).But when you are showing up, you used to_char function to show the timestamp.
In my form, in pre-insert trigger,i am assigning the date.
:block.date:=to_date(sysdate,'dd-mon-yyyy HH24:MI:SS'), still it's inserting only date without timestamp. If i alter the sesstion to datetimestamp, it might insert with timestamp. Everytime i can't alter session to insert timestamp also.
So i went for varchar2 datatype for date column
:block.date:=to_char(sysdate,'dd-mon-yyyy HH24:MI:SS')
So if i use date datatype, i can insert only date without timestamp.
In the form when i query data, whatever is there in date column, it will retrieve it, there i can't use to_char function to show up the data with timestamp.
Could you help me if i use date datatype, still i should be able to insert date with timestamp, if i can, i will change datatype and try that; otherwise, could you provide me any alteranate solution.
Thanks for your help.
|
|
|
Re: comparing date in form 6i [message #518365 is a reply to message #518363] |
Wed, 03 August 2011 07:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Quote:when u inserted data in your example, u inserted only date(without time stamp)
Wrong again. SYSDATE is a function that returns both date & time. Otherwise, how is TO_CHAR supposed to display that value?
What datatype did you choose for :BLOCK.DATE item? I suppose DATE - should be DATETIME.
Storing date values into a VARCHAR2 column is nothing but wrong, VERY wrong, and will cause problems sooner (already did) or later. You can enter anything into a VARCHAR2 column. User's won't be polite and use 24.03.2011 all the time - you'll have 03.24.2011, 11.08.03 (what is "11", what is "08", what is "03"?), 23-may-09, #?!()& and similar. If you don't want these values, you'll have to write (a lot) of code to check what has been entered. Why would you want to do that, when DATE datatype solves that by default?
|
|
|
|
|
|
|
|
|