Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

combing date and time to do max

Status
Not open for further replies.

ss7415

Programmer
Dec 7, 2006
84
US
i have date_add and time_add i want combine the two fields and do a max on both combined. sometimes a record may have the same date, so i need the time to determine the max.


first record
date_add looks like 4/13/2006
time_add look like 20342180

second record
date_add looks like 4/13/2006
time_add look like 20532132




 
I suppose that 20532132
Represents 20:53:21.32

20 hours, 53 minutes, 21.32 seconds?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
It is by far better to store data and time together in one field. Given that it isn't - concatenate the fields (and format into a normal datetime format in the process) then take the max of the whole mess. If you have a lot of records there is no way this will run quickly.
somethinglike
Code:
select max(cast(datefield + ' ' + left(cast(timefield as varchar(8)),2) + ':' + substring (cast(timefield as varchar(8)), 3, 2)  
+ ':' + substring (cast(timefield as varchar(8)), 5, 2)  + ':' + right (cast(timefield as varchar(8)), 2)) as datetime) from table1

if your timefield is not an int field you won't need the cast part for the chuncks fo the time field, you will still need the overall cast.

This is not tested code

Questions about posting. See faq183-874
 
Does anyone know an easy way of doing this? I know of a way, but it's a pain.


[small]"I see pretty girls everywhere I look, everywhere I look, everywhere I look. - Band song on movie "The Ringer"[/small]
<.
 
select max(cast(date_add + ' ' + left(cast(time_add as varchar(8)),2) + ':' + substring (cast(time_add as varchar(8)), 3, 2)
+ ':' + substring (cast(time_add as varchar(8)), 5, 2) + ':' + right (cast(time_add as varchar(8)), 2)) as datetime) from int_table


im getting missing keyword error
 
the formating on the time_add isnt that important, i just need to use date_add and time_add to determine min and max
 
does this work?
Code:
select date_add        as max_date 
     , max(time_add)   as max_time
  from daTable
group
    by date_add
having date_add =
       ( select max(date_add) from daTable )

r937.com | rudy.ca
 
seems like its doing the max date time on the entire database,

if i can just get the two fields to combind and then i can determine how i want to do the max
 
If you don't care about the actual time, you could take your mangled representation of the time and add it as milliseconds to the date. This would produce the WRONG time, but will still produce the right result when combined with the date and the max aggregate. Something like this...

[tt][blue]
Select Id,
Max(DateAdd(Millisecond, Time_Add, Date_Add)) As MaxDateTime
From SomeTable
Group By Id
[/blue][/tt]

The best advice in this thread was given by SQLSister when she advised you to store the combined date and time in the same field.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
sqlsister's sql gave me a

missing keyword error
 
select max(cast(date_add+ ' ' + left(cast(time_add as varchar(8)),2) + ':' + substring (cast(time_add as varchar(8)), 3, 2)
+ ':' + substring (cast(time_add as varchar(8)), 5, 2) + ':' + right (cast(time_add as varchar(8)), 2)) as datetime) from int_table

im getting an missing keyword error

any suggestions
 
Try this...

Code:
select  Max(date_add +
        + Cast(left(cast(time_add as varchar(8)),2) 
        + ':' 
        + substring (cast(time_add as varchar(8)), 3, 2)  
        + ':' 
        + substring(cast(time_add as varchar(8)), 5, 2) 
        + '.' 
        + right(cast(time_add as varchar(8)), 2) As DateTime))
from    int_Table

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
select Max(date_add +
+ Cast(left(cast(time_add as varchar(8)),2)
+ ':'
+ substring (cast(time_add as varchar(8)), 3, 2)
+ ':'
+ substring(cast(time_add as varchar(8)), 5, 2)
+ '.'
+ right(cast(time_add as varchar(8)), 2) As DateTime))
from int_Table

i get invalid data type and it highlights datetime
 
I think I know what the problem is...

Run this:
Select @@Version

post the results here.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Select @@Version

i got ORA-00936: missing expression
 
Ah... you see. That's your problem. You're using Oracle and this forum is devoted exclusively to Microsoft SQL Server.

I recommend you ignore ALL of the advice in this thread, and then create a new question in the Oracle forum.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top