SQL Server 2000 Date Part Sort By Date

	Home Register Search Help Login

Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums

Recent Posts

Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

SQL Server 2000 Date Part Sort By Date

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> Web Development >> ASP and Database >> SQL Server 2000 Date Part Sort By Date

Page: [1]


rrayfield Posts: 54 Joined: 12/28/2004 Status: offline	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 Posts: 6354 Joined: 6/21/2002 From: West Point Utah USA Status: offline	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) _____________________________ Duane Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs. (in reply to rrayfield)


rrayfield Posts: 54 Joined: 12/28/2004 Status: offline	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? (in reply to BeTheBall)


rrayfield Posts: 54 Joined: 12/28/2004 Status: offline	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. (in reply to rrayfield)


BeTheBall Posts: 6354 Joined: 6/21/2002 From: West Point Utah USA Status: offline	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 _____________________________ Duane Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs. (in reply to rrayfield)


rrayfield Posts: 54 Joined: 12/28/2004 Status: offline	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? (in reply to BeTheBall)


Spooky Posts: 26599 Joined: 11/11/1998 From: Middle Earth Status: offline	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 _____________________________ If you arent part of the solution, then there is good money to be made prolonging the problem ��k� Database / DRW help Check out the Tutorials for Frontpage Databases Forum FAQ' s Developers Edition Spooky Login User Registration and Management tool Personalized Frontpage Hosting (in reply to rrayfield)


BeTheBall Posts: 6354 Joined: 6/21/2002 From: West Point Utah USA Status: offline	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? _____________________________ Duane Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs. (in reply to Spooky)


rrayfield Posts: 54 Joined: 12/28/2004 Status: offline	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 (in reply to BeTheBall)


BeTheBall Posts: 6354 Joined: 6/21/2002 From: West Point Utah USA Status: offline	RE: SQL Server 2000 Date Part Sort By Date - 4/1/2006 16:49:55 You need to use GetDate() in place of Date() _____________________________ Duane Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs. (in reply to rrayfield)


rrayfield Posts: 54 Joined: 12/28/2004 Status: offline	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 (in reply to BeTheBall)


BeTheBall Posts: 6354 Joined: 6/21/2002 From: West Point Utah USA Status: offline	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". _____________________________ Duane Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs. (in reply to rrayfield)


rrayfield Posts: 54 Joined: 12/28/2004 Status: offline	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 (in reply to BeTheBall)


rrayfield Posts: 54 Joined: 12/28/2004 Status: offline	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? (in reply to rrayfield)


rrayfield Posts: 54 Joined: 12/28/2004 Status: offline	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. (in reply to rrayfield)


BeTheBall Posts: 6354 Joined: 6/21/2002 From: West Point Utah USA Status: offline	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 _____________________________ Duane Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs. (in reply to rrayfield)


rrayfield Posts: 54 Joined: 12/28/2004 Status: offline	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. (in reply to BeTheBall)


BeTheBall Posts: 6354 Joined: 6/21/2002 From: West Point Utah USA Status: offline	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 _____________________________ Duane Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs. (in reply to rrayfield)


BeTheBall Posts: 6354 Joined: 6/21/2002 From: West Point Utah USA Status: offline	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 _____________________________ Duane Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs. (in reply to rrayfield)


rrayfield Posts: 54 Joined: 12/28/2004 Status: offline	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...... (in reply to rrayfield)


BeTheBall Posts: 6354 Joined: 6/21/2002 From: West Point Utah USA Status: offline	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 _____________________________ Duane Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs. (in reply to rrayfield)


rrayfield Posts: 54 Joined: 12/28/2004 Status: offline	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. (in reply to BeTheBall)


BeTheBall Posts: 6354 Joined: 6/21/2002 From: West Point Utah USA Status: offline	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. _____________________________ Duane Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs. (in reply to rrayfield)


rrayfield Posts: 54 Joined: 12/28/2004 Status: offline	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? (in reply to BeTheBall)


BeTheBall Posts: 6354 Joined: 6/21/2002 From: West Point Utah USA Status: offline	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 _____________________________ Duane Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs. (in reply to rrayfield)


rrayfield Posts: 54 Joined: 12/28/2004 Status: offline	RE: SQL Server 2000 Date Part Sort By Date - 4/1/2006 19:34:18 OK, Duane, YOUR THE MAN!!! 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 (in reply to BeTheBall)

yb2

Posts: 653
Joined: 1/30/2006
Status: offline

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.

(in reply to rrayfield)