change data

[stevelibby]

i wonder if can help me here,
i havent been using database for very long but i am learning more each day, now i started a stats program a couple months back, however i had both date and time as nvarchar, bear it in mind im a beginner, i have now studies and learne how to use date/time functions and mathematics with it.
So, i now want to convert my current to tablefields to datetime, how do i go about this with out loosing data.
please bear in mind i am still a novice.

[stevelibby]

ok never mind about that previous one, i managed to do it in the end by exporting to access, create a sql table with the datein as a datetime and then import the table.
i am now onto my next issue:
after having a selection of dates in the table i wish to group the dates together.
in the table i made the default value getdate(), however this returns the time as well as the date, which makes it difficult for doing a group by date:

SELECT COUNT(*) AS ct, DateIn
FROM tblInsuranceStats
WHERE (DATEDIFF(DAY, DateIn, GETDATE()) < 7)
GROUP BY DateIn

this returns the last 7 days action but it shows as
01/09/2005 13.10
01/09/2005 13.20

how can i get it to group to 01/09/2005 is the something that i could change in the default insetad of getdate()?

[emsaw]

stevelibby,

maybe this?:

select CONVERT(VARCHAR(10), TIS.DateIn, 101), COUNT(*)
from tblInsuranceStats TIS
GROUP BY CONVERT(VARCHAR(10), TIS.DateIn, 101)
WHERE (DATEDIFF(DAY, TIS.DateIn, GETDATE()) < 7) 

-Mark

[stevelibby]

Thank you but that did not seem to work, i have tried many things but i am going back to the point of the data entry on the page in the sql line. how can i get the date to go in that way? if i were able to use date() then that would work, i think. but i think i would need to declare it as a variable first, any ideas?

[emsaw]

what RDBMS are you using? I actually ran that query in MSSQL2000 against a temporary table populated with a few days random data, and it grouped as expected.

Mark

[stevelibby]

Hi Mark
What is RDBMS?
did your date have time in it too?

[stevelibby]

ok, cool i have managed to do it!
slightly long winded but i broke the date down to:
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 tblInsuranceStats
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

This now show the last 14 days of stats!

Ok moving on to my next issue.

from this query i now want to link into another page to show stast for that day!
so, link being?d=15(axample).
when on the next page the sql query to retrieve the results for day 15, i have put as :

SELECT * FROM tblInsuranceStats
WHERE
(Datepart(day,datein) = '::D::')

If i replace the '::D::' with a number it works, so it must be the '::D::' thats the problem?

Why is this not working? Any ideas?