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.
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
WHERE (DATEDIFF(DAY, DateIn, GETDATE()) < 7)
GROUP BY DateIn
this returns the last 7 days action but it shows as
how can i get it to group to 01/09/2005 is the something that i could change in the default insetad of getdate()?
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)
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?
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.
What is RDBMS?
did your date have time in it too?
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())
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
(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?