sql syntax error?

[stevelibby]

Is there anyone there that can help solve this issue of mine, whether it be correct the code or suggest a different way.

Ok From the form that is placed on my web site, it goes into my db to which I have set the field name as DateIn and is datetime field and the default is GetDate().

As you may be aware this returns a date and a time value of as example 19/09/2005 11.39 Great!

Within my reporting page I have a column on left hand side of the page and I want to group the dates by the last 14 days of activity so:

SELECT COUNT(*) AS C, DATEPART(day, DateIn) AS D, DATEPART(month, DateIn) AS M, DATEPART(year, DateIn) AS Y, DATEDIFF(day, DateIn, GETDATE()) AS DIFF FROM Stats GROUP BY DATEPART(day, DateIn), DATEPART(month, DateIn), DATEPART(year, DateIn), DATEDIFF(day, DateIn, GETDATE())HAVING (DATEDIFF(day, DateIn, GETDATE()) < 14)ORDER BY DATEPART(day, DateIn) DESC

Because of the time issue (not being all the same) I have had to break up that DateIn field to D,M,Y this also returns a count for each day.

Now here is where my problem is, from this column I want to able to link the date to the next page to display all records from that day!

I have used the following web bar query?D=15 (I have just used the Day, until I get it right, then ill incorporate the month and year)

SELECT Stats.*
FROM Stats
WHERE (DATEPART(DAY, DateIn)='::D::')

For the life of me I cannot understand why this doesn’t run. lf I were to replace the query to:

SELECT Stats.*
FROM Stats
WHERE (DATEPART(DAY, DateIn)=15) It works!

I have read over the weekend about triggers and cast, not that I really understand them yet, I have a hunch that I need to use a cast. Also with my reading it appears that I should use a smalldatetime instead of a datetime for byte reasons?

Now, I am a complete novice at this but I am getting better, am I on the right lines with the above or do I need to approach this is a different way? If so, what do I do?

Thank you in advance.

[aspdaddy]

SQL isnt great for reporting, its best to do reports on the client app & not the database.

But to get around the time issue with datetime and smalldatetime types, you can select the date only using Format styles:

SELECT Convert(Varchar(11), DateIn,106) as myDate

[stevelibby]

hi aspDaddy
tell me whats the client app? ill try the code now.
The piece of code that you have put is that for the first or second part?

[stevelibby]

i cant for the life of me get it to work :-( , why isnt it working?
I have put it in the first piece of code insted of using datepart, so it now reads:
SELECT CONVERT(Varchar(11), DateIn, 106) AS myDate, COUNT(*) AS Expr1
FROM Stats
WHERE (DATEDIFF(day, DateIn, GETDATE()) < '14')
GROUP BY CONVERT(Varchar(11), DateIn, 106), DATEDIFF(day, DateIn, GETDATE())
ORDER BY DATEDIFF(day, DateIn, GETDATE())

but the second bit still is not working, what am i doing wrong?

[aspdaddy]

WHERE (DATEDIFF(day, DateIn, GETDATE()) < '14')

Try removing the quotes around 14

[stevelibby]

Hi again
That part that i wrote there is working but the how can i collect the string on the other page?

[aspdaddy]

You need to Request the value.
WHERE DATEPART(DAY, DateIn)=" & Request("D")

[stevelibby]

That doesnt seem to work either :-( it must be something im doing wrong.
HHHHHHHEEEEEEELLLLPPPPPPP,
Lets go back to the very start, i need to group the last 14 days.
The fileds is called DateIn, it is a datetime field with the default value of Getdate().
Lets go, what the code to get the last 14 days(group together.!