Dates and Counting - 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 2005 Forums

Transact-SQL (2005)

Dates and Counting

Author

Topic

RobertParker
Starting Member

10 Posts

Posted - 2007-10-30 : 14:24:15

I'm working on a SQL report right now, and I'm having some trouble writing it. I'm very new to SQL.

Basically the report takes in two date parameters, and it also looks at a table of dates.

It has to count the number of entries for each day in the range. Then this data is going to be put in a graph.

So for the Parameters 10/1/07 to 10/4/07, and the following table:
10/1/07
10/1/07
10/2/07
10/4/07
10/4/07
10/4/07

It would list the following:
10/1/07,2
10/2/07,1
10/3/07,0
10/4/07,3

and that data would be used in a chart

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 14:32:46

[code]
SELECT MyDateColumn, COUNT(*)
FROM MyTable
WHERE MyDateColumn >= '20071001'
AND MyDateColumn < '20071005'
GROUP BY MyDateColumn
[/code]
There are additional issues to consider if [MyDateColumn] contains "time" as well as "date" (rather than all values having time = 00:00:00)

Kristen

RobertParker
Starting Member

10 Posts

Posted - 2007-10-30 : 15:32:52

Yeah I've noticed that. I've been trying to figure it out. Everytime I search on the web, I find a really odd way that doesn't work.

I've tried casting as a float, then rounding the time to 00:00 then casting back to a date.

And I also tried CONVERT(CHAR(8), GetDate(), 112), but neither is working the way I want them to.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-30 : 16:26:02

quote:
Originally posted by RobertParker

Yeah I've noticed that. I've been trying to figure it out. Everytime I search on the web, I find a really odd way that doesn't work.

I've tried casting as a float, then rounding the time to 00:00 then casting back to a date.

And I also tried CONVERT(CHAR(8), GetDate(), 112), but neither is working the way I want them to.

You don't have to jump through that many hoops to compare dates.

Just Convert([date],datetime,102) to put them in mm/dd/yyyy format and compare to the same conversion excluding the time stamp. You can also convert them as smalldatetime and compare them that way.

Check BOL for date/time conversion techniques and articles on this site.

What format is your date field in now?

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 16:54:05

"Just Convert([date],datetime,102) ..."

Alhough note that CONVERT to string is a relatively "high CPU cost" and there are less CPU intensive ways to set the time to 00:00:00 on a datetime datatype object.

Kristen

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-30 : 16:59:12

It can't be more expensive that converting everything to float and rounding then back to date though. Why pull a tooth out through your ear is all.

Why not suggest the cheaper CPU method for the OP?

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 17:05:12

"It can't be more expensive that converting everything to float and rounding then back to date though"

Well I don't do it via float either

Here's a very old thread where some timings were considered:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=38940#120953

Kristen

RobertParker
Starting Member

10 Posts

Posted - 2007-10-30 : 17:12:41

This is the current date format: 2007-10-03 17:19:29.467

The problem when I convert the date, is that it messes up how the data should display.

It reads like:

2007-09-30 00:00:00.000, 1
2007-09-30 00:00:00.000, 1
2007-09-30 00:00:00.000, 1

instead of:

2007-09-30, 3

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 18:15:53

Can you show the exact query you are using which is giving that output please

Kristen

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-30 : 19:57:52

Maybe something liek this?

DECLARE @Temp TABLE(MyDate DATETIME)

INSERT @Temp
SELECT DATEADD(HOUR, -6, CURRENT_TIMESTAMP)
UNION ALL SELECT DATEADD(HOUR, -7, CURRENT_TIMESTAMP)
UNION ALL SELECT DATEADD(HOUR, -8, CURRENT_TIMESTAMP)
UNION ALL SELECT DATEADD(HOUR, -14, CURRENT_TIMESTAMP)
UNION ALL SELECT DATEADD(HOUR, -20, CURRENT_TIMESTAMP)
UNION ALL SELECT DATEADD(HOUR, -24, CURRENT_TIMESTAMP)
UNION ALL SELECT DATEADD(HOUR, -32, CURRENT_TIMESTAMP)
UNION ALL SELECT DATEADD(HOUR, -48, CURRENT_TIMESTAMP)


SELECT
	CONVERT(VARCHAR(10), MyDate, 112) AS MyDate,
	MyCount
FROM
(
	SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, MyDate)) AS MyDate, COUNT(*) AS MyCount
	FROM @Temp AS Temp
	GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, MyDate))
) AS T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-31 : 02:22:51

quote:
Originally posted by RobertParker

This is the current date format: 2007-10-03 17:19:29.467

The problem when I convert the date, is that it messes up how the data should display.

It reads like:

2007-09-30 00:00:00.000, 1
2007-09-30 00:00:00.000, 1
2007-09-30 00:00:00.000, 1

instead of:

2007-09-30, 3

You need to omit the time part from dates as specified by Lamprey
Also post the query you used

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources