Home » SQL & PL/SQL » SQL & PL/SQL » Varchar evaluation as timestamp (Forms 6.1 DB 10g PL/SQL 8.0.6)
Varchar evaluation as timestamp [message #319938] Tue, 13 May 2008 07:44 Go to next message
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
Re: Varchar evaluation as timestamp [message #319941 is a reply to message #319938] Tue, 13 May 2008 07:49 Go to previous messageGo to next message
joy_division
Messages: 4642
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?
Re: Varchar evaluation as timestamp [message #319944 is a reply to message #319941] Tue, 13 May 2008 07:57 Go to previous messageGo to next message
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.
Re: Varchar evaluation as timestamp [message #319946 is a reply to message #319944] Tue, 13 May 2008 08:00 Go to previous messageGo to next message
joy_division
Messages: 4642
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.
Re: Varchar evaluation as timestamp [message #319948 is a reply to message #319938] Tue, 13 May 2008 08:05 Go to previous messageGo to next message
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;
Re: Varchar evaluation as timestamp [message #319949 is a reply to message #319948] Tue, 13 May 2008 08:09 Go to previous messageGo to next message
joy_division
Messages: 4642
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 Go to previous messageGo to next message
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
Re: Varchar evaluation as timestamp [message #319954 is a reply to message #319952] Tue, 13 May 2008 08:20 Go to previous message
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
Previous Topic: Derby database
Next Topic: What would happen if we use RETURN clause in Procedure?
Goto Forum:
  


Current Time: Thu Dec 08 00:02:26 CST 2016

Total time taken to generate the page: 0.05881 seconds