DatePart function in ANSI SQL

Hi folks,

How can I re-write the following code in ANSI SQL code:

select cast(datepart(month, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/' +
cast(datepart(day, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/'+ cast(datepart(year, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar), event_instance_id, max(time_stamp)
from usmuser.usm_sli_event_data
where event_instance_id=10019
group by cast(datepart(month, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/' +
cast(datepart(day, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/'+
cast(datepart(year, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar),
event_instance_id
order by event_instance_id

Thanks for your help!
-Parulcould you please explain why?

also, for those of us not patient enough to unravel the intricacies of this delectable code fragment, would you kindly please explain what it's doing|||The code should be portable so it can used be used on other databases as well, not just SQL Server.
Basically, the time_stamp field has number of seconds since 1/1/1970 and the datepart function is calculating the month, day, and year. The goal is to get the last time_stamp per event_instance_id per day.|||unfortunately, your quest will be unsatisfied

date functions are among the more un-robust of the ANSI SQL capabilities

there is practically no hope that you will get exactly the same code to run "on other databases as well, not just SQL Server"

even if we did manage to figure out a way to do what you're doing with ANSI SQL functions (and good luck to you, as i'm going to pass), it probably wouldn't run on SQL Server to start with|||This is what data abstraction layers are for...|||Thanks r937!

Datediff function

select DATEDIFF(second, min (time_stamp), max(time_stamp)) AS responsetime
FROM table1.
Now, on this table1, time_stamp column is defined in datetime format.
Case1
When the min(time_stamp) is 2/17/2005 8:34:55AM, and the max(time_stamp) is
2/17/2005 8:35:12AM ... the responsetime is shown as 0 seconds.
Case2
When the min(time_stamp) is 2/17/2005 12:00:57 PM, and the max(time_stamp)
is 2/17/2005 12:01:01 PM ... the responsetime is shown as 1 seconds.
Iam only interested in finding the difference in seconds. Can sbdy explain
me, if there is a way out ?select DATEDIFF(second, '2/17/2005 8:34:55AM', '2/17/2005 8:35:12AM')
AS responsetime1
select DATEDIFF(second, '2/17/2005 12:00:57 PM', '2/17/2005 12:01:01
PM') AS responsetime2
gives results of 17 and 4. The results you say you get, 0 and 1
are certainly not the right ones, but I've never seen DATEDIFF
give wrong results. Can you post a reproducible script that
gives these wrong results?
It looks like the min and max time_stamp values are not what you think
they are.
Steve Kass
Drew University
SQLWiz wrote:

>select DATEDIFF(second, min (time_stamp), max(time_stamp)) AS responsetime
>FROM table1.
>Now, on this table1, time_stamp column is defined in datetime format.
>Case1
>When the min(time_stamp) is 2/17/2005 8:34:55AM, and the max(time_stamp) is
>2/17/2005 8:35:12AM ... the responsetime is shown as 0 seconds.
>Case2
>When the min(time_stamp) is 2/17/2005 12:00:57 PM, and the max(time_stamp)
>is 2/17/2005 12:01:01 PM ... the responsetime is shown as 1 seconds.
>Iam only interested in finding the difference in seconds. Can sbdy explain
>me, if there is a way out ?
>|||Hi
Iam using this query:
Select log_id, track_id, DATEDIFF(second, min (time_stamp),
max(time_stamp)) AS responsetime, convert(char, min(time_stamp), 110) as
Datevalue, convert(char, min(time_stamp), 108) as timevalue
FROM xmllog_cog where time_stamp > '2005-02-16 00:00:00.000' and
track_id like 'TN-%'and log_id in(
Select distinct log_id from xmllog_cog )group by log_id, track_id
For every log_id, there can be multiple sequence ids ..(like 1,2,3 until x..
where x can vary). Each sequence id has a timestamp attached to it. Iam
interested in finding .. for every log_id what is the time difference betwee
n
1) the timestamp @. max of sequence id (lets say 11)
2) the timestamp @. min of sequence id (will always be 1).
Can you pls suggest what could be done ?
"SQLWiz" wrote:

> select DATEDIFF(second, min (time_stamp), max(time_stamp)) AS responsetim
e
> FROM table1.
> Now, on this table1, time_stamp column is defined in datetime format.
> Case1
> When the min(time_stamp) is 2/17/2005 8:34:55AM, and the max(time_stamp) i
s
> 2/17/2005 8:35:12AM ... the responsetime is shown as 0 seconds.
> Case2
> When the min(time_stamp) is 2/17/2005 12:00:57 PM, and the max(time_stamp)
> is 2/17/2005 12:01:01 PM ... the responsetime is shown as 1 seconds.
> Iam only interested in finding the difference in seconds. Can sbdy explain
> me, if there is a way out ?|||SQLWiz wrote:
> Hi
> Iam using this query:
> Select log_id, track_id, DATEDIFF(second, min (time_stamp),
> max(time_stamp)) AS responsetime, convert(char, min(time_stamp),
> 110) as Datevalue, convert(char, min(time_stamp), 108) as timevalue
> FROM xmllog_cog where time_stamp > '2005-02-16 00:00:00.000' and
> track_id like 'TN-%'and log_id in(
> Select distinct log_id from xmllog_cog )group by log_id, track_id
> For every log_id, there can be multiple sequence ids ..(like 1,2,3
> until x.. where x can vary). Each sequence id has a timestamp
> attached to it. Iam interested in finding .. for every log_id what is
> the time difference between 1) the timestamp @. max of sequence id
> (lets say 11) 2) the timestamp @. min of sequence id (will always be
> 1).
> Can you pls suggest what could be done ?
>
>
> "SQLWiz" wrote:
>
What happens when you run that query without the datediff and use MIN
and MAX as separate columns on the datetime. What values are returned?
David Gugick
Imceda Software
www.imceda.com|||See if these work - they should be equivalent,
but I didn't check them for typos:
select
T1.log_id,
T1.track_id,
datediff(second, min(T1.time_stamp), max(T1.time_stamp)) as timeDiff
from table1 as T1
where T1.sequence_id = (
select min(sequence_id)
from table1 as T2
where T2.log_id = T1.log_id
and T2.track_id = T1.track_id
)
or
T1.sequence_id = (
select max(sequence_id)
from table1 as T2
where T2.log_id = T1.log_id
and T2.track_id = T1.track_id
)
group by T1.log_id, T1.track_id
-- another way to write this that might be faster:
select
T1.log_id,
T1.track_id,
datediff(second, min(T1.time_stamp), max(T1.time_stamp)) as timeDiff
from table1 as T1
where T1.sequence_id in (
select
case when i = 1 then min(sequence_id) else max(sequence_id) end
from table1 as T2
cross join (select 1 as i union all select 2) as OneTwo
where T2.log_id = T1.log_id
and T2.track_id = T1.track_id
)
group by T1.log_id, T1.track_id
-- or this
select
T1.log_id,
T1.track_id,
datediff(second, min(T1.time_stamp), max(T1.time_stamp)) as timeDiff
from table1 as T1
join (
-- a table containing only the min and max
-- sequence_id values along with every
-- (log_id, track_id) pair you need info for
select
T.log_id,
T.track_id,
min(T.sequence_id) as min_or_max_sequence_id
from table1 as T
join xmllog_cog as X
on X.log_id = T.log_id
where <condition>
group by
T.log_id,
T.track_id
union all
select
T.log_id,
T.track_id,
max(T.sequence_id) as min_or_max_sequence_id
from table1 as T
join xmllog_cog as X
on X.log_id = T.log_id
where <condition>
group by
T.log_id,
T.track_id
) as T2
on T2.log_id = T1.log_id
and T2.track_id = T1.track_id
and T2.min_or_max_sequence_id = T1.sequence_id
-- the last condition guarantees you only get rows with
-- smallest or largest sequence_id
group by T1.log_id, T1.track_id
-- or this
select
T1.log_id,
T1.track_id,
datediff(second, min(T1.time_stamp), max(T1.time_stamp)) as timeDiff
from table1 as T1
join (
select
T.log_id,
T.track_id,
case when i = 1 then min(T.sequence_id)
else max(T.sequence_id) end
as min_or_max_sequence_id
from table1 as T
join xmllog_cog as X
on X.log_id = T.log_id
cross join (select 1 as i union all select 2) as OneTwo
where <condition>
group by
T.log_id,
T.track_id
) as T2
on T2.log_id = T1.log_id
and T2.track_id = T1.track_id
and T2.min_or_max_sequence_id = T1.sequence_id
-- the last condition guarantees you only get rows with
-- smallest or largest sequence_id
group by T1.log_id, T1.track_id
-- SK
SQLWiz wrote:
>Hi
>Iam using this query:
>Select log_id, track_id, DATEDIFF(second, min (time_stamp),
>max(time_stamp)) AS responsetime, convert(char, min(time_stamp), 110) as
>Datevalue, convert(char, min(time_stamp), 108) as timevalue
>FROM xmllog_cog where time_stamp > '2005-02-16 00:00:00.000' and
>track_id like 'TN-%'and log_id in(
>Select distinct log_id from xmllog_cog )group by log_id, track_id
>For every log_id, there can be multiple sequence ids ..(like 1,2,3 until x.
.
>where x can vary). Each sequence id has a timestamp attached to it. Iam
>interested in finding .. for every log_id what is the time difference betwe
en
>1) the timestamp @. max of sequence id (lets say 11)
>2) the timestamp @. min of sequence id (will always be 1).
>Can you pls suggest what could be done ?
>
>
>"SQLWiz" wrote:
>
>