Home » SQL & PL/SQL » SQL & PL/SQL » max select on specific date format
max select on specific date format [message #260430] Sun, 19 August 2007 19:23 Go to next message
mgallagher
Messages: 3
Registered: August 2007
Junior Member
Hi all,

My first post to the forum so firstly a big hello to all Smile

So I'm new to sql but would like to perform a select on a table to pull the entry that has the greatest (latest date) for a number of entries. The problem I have is with the format of the date data which is defined in the format 'dd.mm.yyyy hh24:mm:ss). Therefore if I attempt to use the 'max' command this will always return the data incorrectly....

ie for table brpt_ws_dps:

BRPT_NAME BRPT_VAL1 BRPT_VAL4
---------- ---------- -----------
DPSUserLogon Brian \ 4447 25.01.2007 17:27:53
DPSUserLogon Brian \ 4447 19.06.2007 17:30:07
DPSUserLogon Brian \ 4447 31.12.2006 17:34:25
DPSUserLogon Brian \ 4447 24.02.2007 17:40:27


select max(brpt_val4)
from brpt_ws_dps r
where r.brpt_name = 'DPSUserLogon'
and r.brpt_val1 like '%4447'

Would return the value 31.12.2006 17:34:25 rather than the real latest date of 19.06.2007 17:30:07 - the brpt_val4 column is defined as type 'varchar2(50)' as opposed to a 'date' type. I would imagine I need to format the data in some way before selecting the real latest date Embarassed

Many, many thanks.....

[Updated on: Sun, 19 August 2007 19:25]

Report message to a moderator

Re: max select on specific date format [message #260431 is a reply to message #260430] Sun, 19 August 2007 19:36 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
It is treating that as character only.
As 3 comes after 1, the maximum is 31.12.2006

You should do an explicit conversion to date using to_date, then you can get the required.

By
Vamsi
Re: max select on specific date format [message #260434 is a reply to message #260430] Sun, 19 August 2007 21:37 Go to previous messageGo to next message
mgallagher
Messages: 3
Registered: August 2007
Junior Member
Thanks, got it....

     
select max(to_char(to_date(r.brpt_val4,'dd.mm.yyyy HH24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'))
    from brpt_ws_dps r
        where r.brpt_name    = 'DPSUserLogon' 
           and r.brpt_line    > 0 
           and r.brpt_val1 like '%4447'

converts the format and allows the max function to find the latest date Cool

[Updated on: Sun, 19 August 2007 21:49]

Report message to a moderator

Re: max select on specific date format [message #260437 is a reply to message #260430] Sun, 19 August 2007 21:58 Go to previous messageGo to next message
mgallagher
Messages: 3
Registered: August 2007
Junior Member
So now that I have the select to find the max date for a specific user, I look to find the information for multiple users within the same select. Normally I would use the 'in' command to define multiple users but I need to use a wildcard (%) as I only know the user number and not the user name but the user column is in the format '<username> / <user#>'.

i.e. the user column looks like this:

user
------------
brian / 3243

I would imagine that I would again need to use some formating to truncate the column and then search on only the last 4 characters (the user number).

so...

 where user like '%3243'


would give me the entry but I need to define multiple user numbers Razz
Re: max select on specific date format [message #260440 is a reply to message #260430] Sun, 19 August 2007 22:09 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Why does appear to me to be a homework assignment?
What is the business rule behind this requirement?
Re: max select on specific date format [message #260496 is a reply to message #260430] Mon, 20 August 2007 02:53 Go to previous message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
u may user or instead of in
where
user like '%3243'
or user like '%3244' --new another
or user like '%3245' --new another

[Updated on: Mon, 20 August 2007 02:54]

Report message to a moderator

Previous Topic: How to Aggregate records in the same Table
Next Topic: Using recyclebin parameter
Goto Forum:
  


Current Time: Fri Dec 02 16:37:02 CST 2016

Total time taken to generate the page: 0.20360 seconds