combing date and time to do max

[ss7415]

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

 

[r937]

what are the datatypes of these two columns?

r937.com | rudy.ca

 

[ss7415]

date_add is date

time_add is number

 

[gmmastros]

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

 

[ss7415]

yes it does,sir

 

[SQLSister]

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

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

 

[monksnake]

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]
<.

 

[ss7415]

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

 

[ss7415]

the formating on the time_add isnt that important, i just need to use date_add and time_add to determine min and max

 

[r937]

does this work?

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

 

[ss7415]

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

 

[gmmastros]

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

 

[ss7415]

sqlsister's sql gave me a

missing keyword error

 

[ss7415]

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

 

[gmmastros]

Try this...

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

 

[ss7415]

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

 

[gmmastros]

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

 

[ss7415]

Select @@Version

i got ORA-00936: missing expression

 

[gmmastros]

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

 

[ss7415]

oh damn, im very sorry