group by date not time - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

group by date not time

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-25 : 04:05:18

I have the following view:

SELECT     TOP 100 PERCENT Sources.source, COUNT(users.filesource) AS CountOfSourceID, users.mydate
FROM         Sources RIGHT OUTER JOIN
                      users ON Sources.SourceID = users.filesource
GROUP BY Sources.source, users.mydate, users.Isdouble
HAVING      (users.Isdouble = 0)
ORDER BY users.mydate

What this does is group users according to source by date.
The problem is the date field is a datetime field and it is counting them acc. to datetime(and no 2 are the same time). I want it to count according to date. (It should display a date a source and number of records for that date and source)-- but date and not datetime.

How can I change to only count by date and not datetime?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-25 : 04:13:23

will this help?


SELECT     TOP 100 PERCENT Sources.source, COUNT(users.filesource) AS CountOfSourceID, convert( varchar(10), users.mydate, 103)
FROM         Sources RIGHT OUTER JOIN
                      users ON Sources.SourceID = users.filesource
GROUP BY Sources.source, convert( varchar(10), users.mydate, 103), users.Isdouble
HAVING      (users.Isdouble = 0)
ORDER BY users.mydate

Go with the flow & have fun! Else fight the flow

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-25 : 04:14:19

quote:
Originally posted by esthera

ORDER BY users.mydate[/code]

can you use datepart for the field users.mydate?

--------------------
keeping it simple...

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-25 : 04:28:29

No I got this error

Server: Msg 8127, Level 16, State 1, Line 1
Column name 'users.mydate' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-25 : 04:35:36

if you're using my query change
order by convert( varchar(10), users.mydate, 103)

and you really don't need
select TOP 100 PERCENT ...
just select ...

Go with the flow & have fun! Else fight the flow

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-25 : 05:41:55

Thanks that worked. Now the above query I saved as a view. Now I am trying to run the following query on the above query:

SELECT mydate,[source],countofsourceid from countsource WHERE mydate >='10/24/2004' AND myDate < '10/25/2004'

Meaning I want to show all the records matching the date of 10/24/2004. -- But it's now showing the correct records. (It returns 0 records when there are records.)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-25 : 08:16:51

try this:

where DATEADD(Day, DATEDIFF(Day, 0, myDate), 0) = '20041024'

Go with the flow & have fun! Else fight the flow

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-25 : 08:36:42

When running the following:

SELECT mydate,[source],countofsourceid from countsource where DATEADD(Day, DATEDIFF(Day, 0, myDate), 0) = '20041024' I get error:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Also how do I check between 2 dates. (I need to know both a specific date and beteween 2 dates say 10-20-2004 and 10-25-2004)

Thanks for your help.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-25 : 08:47:44

you use
where DATEADD(Day, DATEDIFF(Day, 0, myDate), 0) between '20041024' and '20041026'

what column is char??
because this works for me in northwind:


select customerid, shippeddate, DATEADD(Day, DATEDIFF(Day, 0, shippeddate), 0)
from orders 
where DATEADD(Day, DATEDIFF(Day, 0, shippeddate), 0) = '20040511'

Go with the flow & have fun! Else fight the flow

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-25 : 08:54:21

I think the problem is because this query is a query on the above view.
so we run order by convert( varchar(10), users.mydate, 103)
which converts the date to a char and tehn I cna't compare

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-25 : 09:06:02

man this is getting overcomplicated

where DATEADD(Day, DATEDIFF(Day, 0, cast(yourDate as datetime)), 0) = '20040511'

or simply do
where yourDate = '20040511' -- date in the format you use.

Go with the flow & have fun! Else fight the flow

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-25 : 13:05:55

But still not working..

SELECT mydate,[source],countofsourceid from countsource where DATEADD(Day, DATEDIFF(Day, 0, cast(myDate as datetime)), 0) = '20041024'

Microsoft OLE DB Provider for SQL Server error '80040e07'

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-25 : 15:53:49

quote:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Either you're trying to store dates before 1753, in which case, you are Doomed, or you need to tell it what format your date strings are in to convert them into datetime values. Look at BOL for the various possibilities of date formats using CONVERT.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-26 : 02:21:12

no dates look like this

2004-10-19 13:10:40.187
2004-10-20 13:28:40.543
2004-10-20 13:52:20.373
2004-10-20 13:56:28.857
2004-10-21 05:17:12.030
2004-10-21 05:34:57.640
2004-10-21 11:51:15.670
2004-10-21 11:51:42.043
2004-10-21 12:23:43.293
2004-10-21 12:24:05.060
2004-10-21 13:38:51.750
2004-10-22 02:06:45.780
2004-10-22 02:18:37.623
2004-10-22 02:19:51.967
2004-10-22 02:20:13.293
2004-10-22 02:53:12.700
2004-10-23 14:06:55.250
2004-10-23 14:11:30.780
2004-10-23 14:13:05.513
2004-10-23 14:20:41.920
2004-10-24 00:40:22.513
2004-10-24 00:51:12.920
2004-10-24 01:05:47.967
2004-10-24 01:05:58.543
2004-10-24 01:16:08.903
2004-10-24 01:34:54.327
2004-10-24 08:31:06.653
2004-10-24 20:39:01.153
2004-10-24 20:52:50.640
2004-10-24 21:06:43.810

This is a datetime field. I first run the view

SELECT     TOP 100 PERCENT Sources.source, COUNT(users.filesource) AS CountOfSourceID, convert( varchar(10), users.mydate, 103)
FROM         Sources RIGHT OUTER JOIN
                      users ON Sources.SourceID = users.filesource
GROUP BY Sources.source, convert( varchar(10), users.mydate, 103), users.Isdouble
HAVING      (users.Isdouble = 0)
ORDER BY convert( varchar(10), users.mydate, 103)

View works. I am then trying to run teh following sql on the view:

SELECT mydate,[source],countofsourceid from countsource where DATEADD(Day, DATEDIFF(Day, 0, cast(myDate as datetime)), 0) = '20041024'

This gets an error. How can I combine the 2 statements. depending on the selected date or dates I want to show the date and amount or records listed with that souce by date (not date time)

Kristen
Test

22859 Posts

Posted - 2004-10-26 : 03:53:45

Does this identify any duff data?

SELECT mydate, *
FROM MyTable
WHERE IsDate(mydate) = 0

Perhaps you need to force the date conversion format with
SET DATEFORMAT YMD

Kristen

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-26 : 04:03:11

it returns 0 rows

Kristen
Test

22859 Posts

Posted - 2004-10-26 : 04:13:23

Just rechecked your post, just to be sure we are on the same wavelength I assume you did:

SELECT mydate, *
FROM ~~MyTable~~ countsource
WHERE IsDate(mydate) = 0

Perhaps it doesn't like your constant:

'20041024'

then (but I can't think why ...).

SELECT mydate,[source],countofsourceid
from countsource
where DATEADD(Day, DATEDIFF(Day, 0, cast(myDate as datetime)), 0)
= CAST('20041024' AS datetime)

or if that fails

= CAST('2004-10-24' AS datetime)

(That is the format of your other dates, right?)

or even

= CAST('20041024 00:00:00' AS datetime)

Kristen

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-26 : 04:19:36

It doesn't like any of them.

Anyway to rewrite it to one query vs. a query on a view?
Thanks for your help i'm new to sql server.

Kristen
Test

22859 Posts

Posted - 2004-10-26 : 08:32:32

So have I udnerstood this correctly that basically you are wanting to do a query like this (I've put your previous query, which you indicated you had made into a VIEW, as a sub-query)


SELECT mydate,
       [source],
       countofsourceid
FROM
(
    SELECT TOP 100 PERCENT 
           Sources.source, 
           COUNT(users.filesource) AS CountOfSourceID, 
           convert( varchar(10), users.mydate, 103) AS mydate
    FROM   Sources 
           RIGHT OUTER JOIN users
               ON Sources.SourceID = users.filesource
    GROUP BY Sources.source, 
           convert( varchar(10), users.mydate, 103), 
           users.Isdouble
    HAVING users.Isdouble = 0
    ORDER BY convert( varchar(10), users.mydate, 103)
) T
where DATEADD(Day, DATEDIFF(Day, 0, cast(myDate as datetime)), 0) = '20041024'

Kristen

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-26 : 08:37:39

yes exactly. but when I run what you wrote I get:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Why? How else could I do this?

Kristen
Test

22859 Posts

Posted - 2004-10-26 : 08:44:58

OK, now I get it - I hope!

select convert( varchar(10), GetDate(), 103)

is giving "26/10/2004"

I presume your server is NOT set to UK date format, so SQL is not happy to convert this back to a date.

You'd be much better off to stick with datetime format throughout, rather than converting to char. I would imagine you can scrap the ORDER BY too (I've marked it as strike-out)


SELECT mydate,
       [source],
       countofsourceid
FROM
(
    SELECT TOP 100 PERCENT
           Sources.source, 
           COUNT(users.filesource) AS CountOfSourceID, 
           DATEADD(Day, DATEDIFF(Day, 0, users.mydate), 0) AS mydate
    FROM   Sources 
           RIGHT OUTER JOIN users
               ON Sources.SourceID = users.filesource
    GROUP BY Sources.source, 
           DATEADD(Day, DATEDIFF(Day, 0, users.mydate), 0), 
           users.Isdouble
    HAVING users.Isdouble = 0
    ORDER BY DATEADD(Day, DATEDIFF(Day, 0, users.mydate), 0)
) T
where myDate = '20041024'

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources