OraFAQ Forum: Forms » comparing date in form 6i

Home » Developer & Programmer » Forms » comparing date in form 6i

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

comparing date in form 6i [message #518307]

Wed, 03 August 2011 01:18

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.

Report message to a moderator

Re: comparing date in form 6i [message #518316 is a reply to message #518307]

Wed, 03 August 2011 02:04

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Do not SUBSTR DATE datatype - TRUNC it in order to remove time part.

Report message to a moderator

Re: comparing date in form 6i [message #518325 is a reply to message #518316]

Wed, 03 August 2011 03:01

goparaju
Messages: 54
Registered: June 2011

Member

Hi,

Thanks for your reply. As my 'date' is varachar2 datatype, not date datatype. I can't use trunc, so i am using 'substr'.

Could you help me how to solve my problem.

Thanks.

Report message to a moderator

Re: comparing date in form 6i [message #518327 is a reply to message #518325]

Wed, 03 August 2011 03:07

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

my 'date' is varachar2 datatype

Huh, right - now I saw it. That is a bad idea. Why is it not a DATE? Modify it!

What happens when you run the same statement in SQL*Plus? Does it return anything?

select * from your_table
where substr(date, 1, 11) = '<enter date value exactly as you do in a form>'

Report message to a moderator

Re: comparing date in form 6i [message #518354 is a reply to message #518327]

Wed, 03 August 2011 06:22

goparaju
Messages: 54
Registered: June 2011

Member

Yes, if i run that query in sql*plus, it's working fine. Not working in form builder.

I put the 'date' datatype as varchar2 since i have to insert timestamp also. If i use date datatype, i can't insert timestamp.

Kindly help.

Thanks

Report message to a moderator

Re: comparing date in form 6i [message #518355 is a reply to message #518354]

Wed, 03 August 2011 06:27

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

If i use date datatype, i can't insert timestamp.

Says who? I can.

SQL> create table test_date (col date);

Table created.

SQL> insert into test_date (col) values (sysdate);

1 row created.

SQL> select to_char(col, 'dd.mm.yyyy hh24:mi:ss') from test_date;

TO_CHAR(COL,'DD.MM.
-------------------
03.08.2011 13:23:54

SQL>

Show us the way you did that, and we'll (probably) show you what you did wrong. I suspect (if you tested that in SQL*Plus) that NLS_DATE_FORMAT prevented time to be displayed.

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> select col from test_date;

COL
----------
03.08.2011

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select col from test_date;

COL
-------------------
03.08.2011 13:23:54

SQL>

Report message to a moderator

Re: comparing date in form 6i [message #518363 is a reply to message #518355]

Wed, 03 August 2011 07:17

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.

Report message to a moderator

Re: comparing date in form 6i [message #518365 is a reply to message #518363]

Wed, 03 August 2011 07:29

Littlefoot
Messages: 21823
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?

Report message to a moderator

Re: comparing date in form 6i [message #518372 is a reply to message #518355]

Wed, 03 August 2011 07:54

goparaju
Messages: 54
Registered: June 2011

Member

Thanks for your immense help.

I am using varchar2 in database and datablock for date column.

Could you help me if i use date datatype in database and datetime in datablock, if i use below in pre-insert trigger,as u said it will insert with timestamp.

PRE-INSERT Trigger: :block.date:=sysdate.

As u said, it will insert with timestamp, but you can't see until session was altered in database, but can u see with time stamp in form.

1.if i query data, will it show with timestamp.
2.if i query data by entering only date(without timestamp), will it show the data.[By using trunc(date)=:block.date in 'WHERE' condition of block]

Kindly help me.

Once again, thanks for your help.

Report message to a moderator