SQL Server 2000 Date Part Sort By Date (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

rrayfield -> SQL Server 2000 Date Part Sort By Date (3/31/2006 23:53:46)

I currently have a db with about 90,000 records, One of the fields stored is a date/time field. Like 3/31/2006 11:33:21 PM , I would like to query to show me the totals for the last 7 days... with a customer count

Like

3/31/2006 300 customers
3/30/2006 298 customers
3/29/2006 312 customers

and so on...

For the last 7 days...


Anyone have a clue how to do this? With a SP or View?

Any Help Would BE GREATLY Appreciated!

Thanks
Ryan



BeTheBall -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 9:38:30)

Something like this:

SELECT Count(Customers) AS myCount, FormatDateTime(DateField,2) AS myDate FROM tableName WHERE DateDiff("d",DateField, Date()) < 8 AND DateDiff("d",DateField, Date()) > 0 GROUP BY FormatDateTime(DateField,2)



rrayfield -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 13:58:23)

When I try to use this in a view in SQL server 2000 is come back and says 'formatdatetime' is not a recognized function name... Should I be putting it straight in my asp code?



rrayfield -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 14:12:27)

When I put the statement, in my asp code instead of a view it gives the same error msg.



BeTheBall -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 14:20:18)

Evidently FormatDateTime does not work in SQL Server. Give this a read and see if it helps:

http://www.aspfaq.com/show.asp?id=2460



rrayfield -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 14:34:01)

Reading Over that, It seems that this is way over my head, I don't understand where to put what? I tried the Convert, but where is my date field? should I change the GetDate() to my db field?



Spooky -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 16:32:48)

Try the same thing without the FormatDateTime function ? thats only for formating the displayed date. We can do that after in the asp code if successful



BeTheBall -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 16:38:45)

Won't the fact that his date field is date and time mess up the grouping?



rrayfield -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 16:44:33)

I tried the query
SELECT Count(Customers) AS myCount, FormatDateTime(DateField,2) AS myDate FROM tableName WHERE DateDiff("d",DateField, Date()) < 8 AND DateDiff("d",DateField, Date()) > 0 GROUP BY FormatDateTime(DateField,2)

and too out formatdatetime - then I get an error on Date(), It does not like it, illeagal function



BeTheBall -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 16:49:55)

You need to use GetDate() in place of Date()



rrayfield -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 16:58:56)

I am having a massive brain cramp...

I am now getting a compilation error like this

Microsoft VBScript compilation error '800a03ee'

Expected ')'



objRS.Open "SELECT Count(ID) AS myCount, (Timestamp,2) AS myDate FROM tableName WHERE DateDiff("d",Timestamp, GetDate()) < 8 AND DateDiff("d",Timestamp, GetDate()) > 0 GROUP BY (Timestamp,2)", objConn, adCmdTable"
-------------------------------------------------------------------------------------------------^


because of the "" around the "d"

Here is the current code I have

objRS.Open "SELECT Count(ID) AS myCount, (Timestamp,2) AS myDate FROM tableName WHERE DateDiff("d",Timestamp, GetDate()) < 8 AND DateDiff("d",Timestamp, GetDate()) > 0 GROUP BY (Timestamp,2)", objConn, adCmdTable"


Thanks for all you help




BeTheBall -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 17:02:15)

Try single quotes around the d, i.e., 'd' instead of "d".



rrayfield -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 17:09:53)

OK Now I am getting

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

Line 1: Incorrect syntax near ','.

........

SQL Line looks like this now

objRS.Open "SELECT Count(ID) AS myCount, (Timestamp,2) AS myDate FROM tableName WHERE DateDiff('d',Timestamp, GetDate()) < 8 AND DateDiff('d',Timestamp, GetDate()) > 0 GROUP BY (Timestamp,2)", objConn, adCmdTable




rrayfield -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 17:12:29)

I think It may be beacuse I took off the formatdatetime in from of the (Timestamp,2) and it is confused??

Maybe I need to tak eoff the () arount timestamm and drop the 2 since I am not using the formatdatetime anymore

Does this sound right?



rrayfield -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 17:13:56)

OK I did the above and now I am getting


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

Invalid parameter 1 specified for datediff.



BeTheBall -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 17:24:39)

Timestamp is a reserved word. The following should work, but I think the fact that the date field contains both date and time may cause the group by to not work properly.

objRS.Open "SELECT Count(ID) AS myCount, ([Timestamp]) AS myDate FROM tableName WHERE DateDiff('d',[Timestamp], GetDate()) < 8 AND DateDiff('d',[Timestamp], GetDate()) > 0 GROUP BY [Timestamp]", objConn, adCmdTable



rrayfield -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 17:34:40)

OK, I added the brackets around the field [Timestamp] and it is still giving the following error


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

Invalid parameter 1 specified for datediff.



BeTheBall -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 17:45:06)

Forgive me if it sounds like I am guessing a bit, that's because I am. It seems newer versions of SQL require no quotes around the d. See if removing them completely helps any.

objRS.Open "SELECT Count(ID) AS myCount, ([Timestamp]) AS myDate FROM tableName WHERE DateDiff(d,[Timestamp], GetDate()) < 8 AND DateDiff(d,[Timestamp], GetDate()) > 0 GROUP BY [Timestamp]", objConn, adCmdTable



rrayfield -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 18:11:50)

Guessing is GREAT! I am getting closer to figuring this out. OK

I got the results back tot he page BUT it is returning all of the customers for the last 7 days, I think because the time is different on the records in the timestamp field, it is not counting coretly because it see it as different dates?? even though the date is the same the time is throuing it off. Here is a smal exampel of teh 1000's it returned..

Note that the first is the total and the date is well the date, and yes it was possible for records to have the exact same timestamp down to the second, that is why it is totaling some of them...

So now haw to get it to look at just the date and retun just that? and the sorting seems to be off

1 - 3/31/2006 3:34:43 PM

1 - 3/28/2006 8:25:25 PM

1 - 3/31/2006 6:50:26 AM

3 - 3/27/2006 3:32:26 PM

1 - 3/30/2006 5:01:06 PM

1 - 3/28/2006 9:23:43 AM

1 - 3/28/2006 12:05:53 AM

5 - 3/31/2006 1:51:36 PM

5 - 3/31/2006 2:51:53 PM

1 - 3/28/2006 12:21:03 PM

2 - 3/27/2006 1:56:39 PM

1 - 3/26/2006 6:44:57 PM

1 - 3/31/2006 5:03:57 PM

2 - 3/26/2006 8:22:31 PM

3 - 3/30/2006 12:21:40 PM

2 - 3/31/2006 11:18:06 AM

3 - 3/30/2006 2:58:45 PM

I fixed the sorting by adding "ORDER BY Timestamp desc" to the end



BeTheBall -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 18:17:32)

Not sure this will do it, but worth a try:

objRS.Open "SELECT Count(ID) AS myCount, ([Timestamp]) AS myDate FROM tableName WHERE DateDiff(d,[Timestamp], GetDate()) < 8 AND DateDiff(d,[Timestamp], GetDate()) > 0 GROUP BY DatePart(d,[Timestamp])", objConn, adCmdTable



rrayfield -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 18:21:01)

OK Hold everything,

I had another field in my DB that is called searchdate, but is is a navchar field...

I change around the sql to use this field instaed since it only hold mm/dd/yyyy and it worked fine... I did not think that it would since it was the wrong field type. So I can work with this.. It still does not solve the problem on how to do it on a datetime field and I would like to figure that out...

I will try any other recommendations......



BeTheBall -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 18:25:26)

Did you try the one I just posted?

http://www.frontpagewebmaster.com/fb.asp?m=319180



rrayfield -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 18:34:39)

It did not seem to like that either, I had

objRS.Open "SELECT Count(ID) AS myCount, ([Timestamp]) AS myDate, Sum(price) AS total1 FROM customers WHERE DateDiff(d,[Timestamp], GetDate()) < 8 AND DateDiff(d,[Timestamp], GetDate()) > 0 GROUP BY DatePart(d,[Timestamp]) ORDER BY Timestamp desc", objConn, adCmdTable

and got the error



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

Column 'customers.Timestamp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.





BeTheBall -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 18:38:42)

OK, we might be able to beat that:

objRS.Open "SELECT Count(ID) AS myCount, ([Timestamp]) AS myDate, DatePart(d,[Timestamp]) AS theDay, Sum(price) AS total1 FROM customers WHERE DateDiff(d,[Timestamp], GetDate()) < 8 AND DateDiff(d,[Timestamp], GetDate()) > 0 GROUP BY DatePart(d,[Timestamp]) ORDER BY Timestamp desc", objConn, adCmdTable

I added DatePart(d,[Timestamp]) AS theDay to the Select statement that may make the SQL work. You probably won't want to actually display just theDay, but you should have control over that in your ASP.



rrayfield -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 18:45:40)

I added that to my sql statement and I get the same responce, do you think it something to do with the Group By section?



BeTheBall -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 19:11:01)

You just insist on making me improve my SQL skill don't you. [;)]

I think you have to use convert. See where this gets you:

SELECT convert(varchar(10),[Timestamp],101) AS myDate, Count(ID) AS myCount, Sum(price) AS total1 FROM customers WHERE DateDiff(d,[Timestamp], GetDate()) < 8 AND DateDiff(d,[Timestamp], GetDate()) > 0 GROUP BY convert(varchar(10),[Timestamp],101) ORDER BY convert(varchar(10),[Timestamp],101) desc", objConn, adCmdTable



rrayfield -> RE: SQL Server 2000 Date Part Sort By Date (4/1/2006 19:34:18)

OK, Duane, YOUR THE MAN!!! [:D]

That worked like a charm, so I guess you have to use the convert method... I hope taht this helps other people out there also, Thanks for all your time, this really helps me out.

Ryan



yb2 -> RE: SQL Server 2000 Date Part Sort By Date (4/2/2006 7:57:25)

since you're on SQL2k, you could try this...

this user defined function helps to format dates - anyone can use it. If you want British style dates just swap round the convert lines I've marked "day" and "month" 
use yourdatabasenamehere
go

create function dbo.DateFormat_udf
(
@indate smalldatetime
, @seperator char(1) = '-'
)
RETURNS nchar(20)
AS
 BEGIN
	RETURN
	CONVERT( nvarchar(20), LEFT(DateName(month, @indate) ), 3)) --month
	+ @seperator
	+ CONVERT( nvarchar(20), datepart(dd, @indate) -- day
	+ @seperator
	+ CONVERT( nvarchar(20), datepart(yyyy, @indate) ) --year
 END


and to optimise your query and help with security, put it into a stored procedure 
use yourdatabasenamehere
go

CREATE PROC dbo.GetCustomerCountByDate_usp

AS

	SET NOCOUNT ON

DECLARE @today smalldatetime, @weekago smalldatetime
SET @today = GETDATE()
SET @weekago = DATEADD(day, -7, @today)

SELECT Count(Customers) AS myCount
, dbo.DateFormat_udf( DateField, ' ') AS myDate
 FROM tableName 
WHERE DateField BETWEEN  @ weekago AND @ today
GROUP BY DateField 

	SET NOCOUNT OFF


If you know you are going to run this query often you might want to put an index on the datefield column, and that index will get used for the BETWEEN clause.

I thought I'd also mention that Timestamps only tell you that a column has been changed, it doesn't actually tell you any useful date information - it's really for things like replication and Full-Text indexing. Just incase you didn't know.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.0625