homepage Welcome to WebmasterWorld Guest from 54.227.141.230
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Website
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
change data
stevelibby




msg:1580681
 12:47 pm on Sep 14, 2005 (gmt 0)

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




msg:1580682
 5:44 pm on Sep 14, 2005 (gmt 0)

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




msg:1580683
 11:30 pm on Sep 14, 2005 (gmt 0)

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




msg:1580684
 9:03 am on Sep 15, 2005 (gmt 0)

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




msg:1580685
 3:04 pm on Sep 15, 2005 (gmt 0)

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




msg:1580686
 3:18 pm on Sep 15, 2005 (gmt 0)

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

stevelibby




msg:1580687
 6:09 pm on Sep 15, 2005 (gmt 0)

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?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved