homepage Welcome to WebmasterWorld Guest from 50.19.172.0
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
change data
stevelibby

10+ Year Member



 
Msg#: 33 posted 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

10+ Year Member



 
Msg#: 33 posted 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

5+ Year Member



 
Msg#: 33 posted 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

10+ Year Member



 
Msg#: 33 posted 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

5+ Year Member



 
Msg#: 33 posted 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

10+ Year Member



 
Msg#: 33 posted 3:18 pm on Sep 15, 2005 (gmt 0)

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

stevelibby

10+ Year Member



 
Msg#: 33 posted 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.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved