OraFAQ Forum: SQL & PL/SQL » Varchar evaluation as timestamp

Home » SQL & PL/SQL » SQL & PL/SQL » Varchar evaluation as timestamp (Forms 6.1 DB 10g PL/SQL 8.0.6)

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

Varchar evaluation as timestamp [message #319938]

Tue, 13 May 2008 07:44

didiera
Messages: 134
Registered: August 2007
Location: Mauritius

Senior Member

Hello, I have a question:

I have a table with 2 varchar fields that hold allowed access time ranges (lower and upper bound). Data is stored in the follwing format 'HH:MM'; quite simple. I'm writing a function that will validate access to forms for a given user against this lower and upper bound; i.e I use sysdate as parameter to obtain the current time (login-attempt time) and try to evaluate this with my varchar fields in the table to grant or deny access (where current time falls between these bounds) ...see what I mean?
So, how would you implement this since when I do a to_char() with the time part of sysdate and try to select where that result is between lower and upper_bound, it obviously doesn't work?

thanks in advance

Report message to a moderator

Re: Varchar evaluation as timestamp [message #319941 is a reply to message #319938]

Tue, 13 May 2008 07:49

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

didiera wrote on Tue, 13 May 2008 08:44

...
varchar fields in the table to grant or deny access (where current time falls between these bounds) ...see what I mean?

No.

Quote:

So, how would you implement this since when I do a to_char() with the time part of sysdate and try to select where that result is between lower and upper_bound, it obviously doesn't work?

Compare DATEs to DATEs and VARCHARs to VARCHARs. When you attempt to compare a string to a DATE you are comparing two different datatype. It's like comparing a CLOB to a BOOLEAN. Would you do that?

Report message to a moderator

Re: Varchar evaluation as timestamp [message #319944 is a reply to message #319941]

Tue, 13 May 2008 07:57

didiera
Messages: 134
Registered: August 2007
Location: Mauritius

Senior Member

errr ok.

Did I forget to mention that :

1] For business rules, I cannot change the data types in the table
(don't ask why, it's just this way)

2] I really need to work my may around this and get to compare the current time to these two fields in order to grant/deny access.

I would be glad if you could suggest the use of some standard function to do the cross-datatype magic. I've actually tried to_timestamp(), to_date and stuff but the problem is that when applied on to the varchar fields, it always yielded a 'default' date part like 1/5/2008 to them.

Report message to a moderator

Re: Varchar evaluation as timestamp [message #319946 is a reply to message #319944]

Tue, 13 May 2008 08:00

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

We need more.

Show us what you are talking about along with a describe of the tables involved and the SQL statement you are using. I really have no idea what you are talking about.

Report message to a moderator

Re: Varchar evaluation as timestamp [message #319948 is a reply to message #319938]

Tue, 13 May 2008 08:05

didiera
Messages: 134
Registered: August 2007
Location: Mauritius

Senior Member

Here goes, I paste the SQL so that it might clear out things

access_hour_start and access_hour_end are the two varchar fields that define for any given user, their access span during any given day.

select u.access_granted into vgrnt
   from application_username u ,application_grantee g
   where u.username = p_user
   and instr(u.access_day,to_char(sysdate,'d')) > 0
   and to_char(sysdate,'HH:MM') between u.access_hour_start and u.access_hour_end
   and u.access_granted = 'Y'
   and u.username = g.username
   and g.program_name = p_prgm;

Report message to a moderator

Re: Varchar evaluation as timestamp [message #319949 is a reply to message #319948]

Tue, 13 May 2008 08:09

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

didiera wrote on Tue, 13 May 2008 09:05

select u.access_granted into vgrnt
   from application_username u ,application_grantee g
   where u.username = p_user
   and instr(u.access_day,to_char(sysdate,'d')) > 0
   and to_char(sysdate,'HH:MM') between u.access_hour_start and u.access_hour_end
   and u.access_granted = 'Y'
   and u.username = g.username
   and g.program_name = p_prgm;

First, to_char(sysdate,'d') returns a CHAR, so you cannot compare a CHAR to zero.

I asked for a describe of the tables which you did not provide. What is the datatype of access_day? What's the problem with comparing a TO_CHAR of the date to VARCHARs? It works for me. Maybe you need to use the correct format mask, like HH24?

orcl10g SCOTT>l
  1* select 1 from dual  where to_char(sysdate,'hh24:mm') between '08:12' and '14:07'
orcl10g SCOTT>/

         1
----------
         1

[Updated on: Tue, 13 May 2008 08:14]

Report message to a moderator

Re: Varchar evaluation as timestamp [message #319952 is a reply to message #319949]

Tue, 13 May 2008 08:17

didiera
Messages: 134
Registered: August 2007
Location: Mauritius

Senior Member

Ok

I'll dig on the format mask thing then. The reason I didn't give table description is just because only two fields are involved and yes, they are of type varchar.

thanks anyway

Report message to a moderator

Re: Varchar evaluation as timestamp [message #319954 is a reply to message #319952]

Tue, 13 May 2008 08:20

didiera
Messages: 134
Registered: August 2007
Location: Mauritius

Senior Member

I just tested your code sample, I think it's gonna be all right.

Thanks a lot friend! Cool

Report message to a moderator

Previous Topic:	Derby database
Next Topic:	What would happen if we use RETURN clause in Procedure?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 04 19:46:34 CST 2024