SQL 2000 Query help

Can the following query be streamlined by CONVERT instead of CAST? If so how? No rush on this I'm in a learning phase with SQL and I'm getting quite confused with aggregation and datetime functions.


select cast(
cast(yy as nvarchar(4))+'-'+
cast(mm as nvarchar(2))+'-'+
cast(dd as nvarchar(2))+' '+
cast(hh as nvarchar(2))+':'+
cast(mi15*15 as nvarchar(2))
as datetime) dtCompleted, counts
into #T
from
(select
datepart(yy, dtcompleted) yy,
datepart(mm, dtcompleted) mm,
datepart(dd, dtcompleted) dd,
datepart(hh, dtcompleted) hh,
datepart(mi,dtcompleted)/15 mi15,
count(dtcompleted) counts
from sds (nolock)
where dtcompleted between '2008-08-01' and '2008-08-08'
group by
datepart(yy, dtcompleted),
datepart(mm, dtcompleted),
datepart(dd, dtcompleted),
datepart(hh, dtcompleted),
datepart(mi,dtcompleted)/15
)
order by 1
 
select * from #t
select min(counts),max(counts),avg(counts),sum(counts) from #t
 
drop table #t
drei22Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
yuchingConnect With a Mentor Commented:
Perhaps can try this

select Convert(
      datetime,
      (Convert(nvarchar(4), yy)+'-'+
      Convert(nvarchar(2), mm)+'-'+
      Convert(nvarchar(2), dd)+' '+
      Convert(nvarchar(2), hh)+':'+
      Convert(nvarchar(2), mi15*15))
) dtCompleted, counts
into #T
from
(select
datepart(yy, dtcompleted) yy,
datepart(mm, dtcompleted) mm,
datepart(dd, dtcompleted) dd,
datepart(hh, dtcompleted) hh,
datepart(mi,dtcompleted)/15 mi15,
count(dtcompleted) counts
from sds (nolock)
where dtcompleted between '2008-08-01' and '2008-08-08'
group by
datepart(yy, dtcompleted),
datepart(mm, dtcompleted),
datepart(dd, dtcompleted),
datepart(hh, dtcompleted),
datepart(mi,dtcompleted)/15
)
order by 1
0
All Courses

From novice to tech pro — start learning today.