Strange result from datetime column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Strange result from datetime column

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-03-10 : 07:33:40

Hi, I've got this query

SELECT startTime, COUNT(startTime) AS Expr1
FROM tblJobDetail
GROUP BY startTime
ORDER BY startTime

This is a sample of the results

10/03/2009 01:32:40 2
10/03/2009 01:32:40 1
10/03/2009 01:32:40 2
10/03/2009 01:32:40 2
10/03/2009 01:32:41 1
10/03/2009 01:32:41 2
10/03/2009 01:32:41 1
10/03/2009 01:32:41 7
10/03/2009 01:32:41 3

I want the number of times each distinct time is present?

Thanks

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-10 : 07:48:13

SELECT startTime, COUNT(distinct startTime) AS Expr1
FROM tblJobDetail
GROUP BY startTime
ORDER BY startTime

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-03-10 : 07:50:37

Hi,

That gives the same result.

I think its because the datetime column includes milliseconds and I only want to group up to second level.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-10 : 07:52:22

Can u post ur table data and expected Output?

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-03-10 : 07:56:25

This is output

10/03/2009 01:32:40 2
10/03/2009 01:32:40 1
10/03/2009 01:32:40 2
10/03/2009 01:32:40 2
10/03/2009 01:32:41 1
10/03/2009 01:32:41 2
10/03/2009 01:32:41 1
10/03/2009 01:32:41 7
10/03/2009 01:32:41 3

It should be

10/03/2009 01:32:40 7
10/03/2009 01:32:41 14

Thanks

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-10 : 07:57:35

Try this once,
select startdate,sum(value) from table group by startdate

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-10 : 07:57:40

[code]SELECT convert(varchar,startTime,108), count(distinct convert(varchar,startTime,108)) AS Expr1
FROM tblJobDetail
GROUP BY startTime
ORDER BY startTime[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-10 : 07:57:55

SELECT startTime, sum(result) AS Expr1
FROM @t
GROUP BY startTime
ORDER BY startTime

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-03-10 : 08:06:32

Hi sakets

SELECT convert(varchar,startTime,108), count(distinct convert(varchar,startTime,108)) AS Expr1
FROM tblJobDetail
GROUP BY startTime
ORDER BY startTime

This gives

01:32:40 1
01:32:40 1
01:32:40 1
01:32:40 1
01:32:41 1
01:32:41 1
01:32:41 1
01:32:41 1
01:32:41 1
01:32:41 1
01:32:41 1
01:32:41 1
01:32:41 1

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-10 : 08:10:59

did u try my query?

declare @Temp table ( startdate datetime, val int)
insert into @temp select '10/03/2009 01:32:40' ,2
insert into @temp select '10/03/2009 01:32:40' ,1
insert into @temp select '10/03/2009 01:32:40' ,2
insert into @temp select '10/03/2009 01:32:40' ,2
insert into @temp select '10/03/2009 01:32:41', 1
insert into @temp select '10/03/2009 01:32:41', 2
insert into @temp select '10/03/2009 01:32:41', 1
insert into @temp select '10/03/2009 01:32:41', 7
insert into @temp select '10/03/2009 01:32:41' ,3

select startdate,sum(val) from @Temp group by startdate

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-10 : 08:12:05

Sorry. Try this,

SELECT convert(varchar,startTime,120), count(distinct convert(varchar,startTime,120)) AS Expr1
FROM tblJobDetail
GROUP BY convert(varchar,startTime,120)
ORDER BY convert(varchar,startTime,120)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 13:19:05

[code]
SELECT DATEADD(ss,DATEDIFF(ss,DATEADD(dd,DATEDIFF(dd,0,startTime),0),startTime),DATEADD(dd,DATEDIFF(dd,0,startTime),0)) AS DateVal, COUNT(startTime) AS Expr1
FROM tblJobDetail
GROUP BY DATEADD(ss,DATEDIFF(ss,DATEADD(dd,DATEDIFF(dd,0,startTime),0),startTime),DATEADD(dd,DATEDIFF(dd,0,startTime),0))
ORDER BY DateVal
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 13:23:25

quote:

Originally posted by sakets_2000

Sorry. Try this,

SELECT convert(varchar,startTime,120), count(distinct convert(varchar,startTime,120)) AS Expr1
FROM tblJobDetail
GROUP BY convert(varchar,startTime,120)
ORDER BY convert(varchar,startTime,120)

you dont get correct sorted results when you convert date to varchar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources