Unanswered: SQL Server 2005 Date/Time Problem, Please Help
I have two issues and I hope someone can help me with it. I have been on this problem for 3 days now, And I can't resolve it.
I have a database in access 2003 that is the front-end and the backend on sql server 2005.
I have a table called group_section, that has 3 fields begintime, endtime and date.
Before I linked the access application with sql server the data type of all 3 were as follow: begintime was date/time, medium time; endtime was date/time medium time; and the date was date/time medium date.
When I upsized the database, the datetypes in sql server shows the fields data as folow:
# the date shows the output as 11/10/2005 12:00:00 AM instead of 11-Oct-05
# the begintime output shows as 12/30/1899 6:00:00 PM instead of 6:00 PM
# the endtime shows the output as 12/30/1899 9:00:00 PM instead of 9:00 PM
1. How can I change the date/time to be the same as in access ?????
The reason why i want to format to be the same as in access is because I have to calculate the time difference between the endtime and begintime.
I have another table called teacher, and I have to make a query in which I must calculate the time difference and make a calculated field that calculate the time difference multiplied with the teacher_hourlyrate.
I have made the query in access that came out good( I made it in the design view) :
SELECT Teacher.Teacher_Name, Round(([Group_Endtime]-[Group_Begintime])*24,2) AS HoursWorked, [HoursWorked]*[Teacher_Hourlyrate] AS Total, Group_Section.Group_Id, Teacher.Teacher_Hourlyrate, Group_Section.Group_Date, Course.Course_Name
FROM Teacher INNER JOIN ((Course INNER JOIN Group ON Course.Course_Id = Group.Course_Id) INNER JOIN Group_Section ON Group.Group_Id = Group_Section.Group_Id) ON Teacher.Teacher_Id = Course.Teacher_Id;
But I have to make this same query in SQL Server and it doesn't come out.
Because when I make the view in SQL Server,( i also used the design view);
SELECT dbo.Teacher.Teacher_Name, dbo.Group_Section.Group_Endtime - dbo.Group_Section.Group_Begintime AS HoursWorked, dbo.Group_Section.Group_Id,
dbo.Teacher.Teacher_Hourlyrate, dbo.Group_Section.Group_Date, dbo.Course.Course_Name
FROM dbo.Teacher INNER JOIN
dbo.Course INNER JOIN
dbo.Group ON dbo.Course.Course_Id = dbo.Group.Course_Id INNER JOIN
dbo.Group_Section ON dbo.Group.Group_Id = dbo.Group_Section.Group_Id ON dbo.Teacher.Teacher_Id = dbo.Course.Teacher_Id
The Hoursworked shows this output : 1/1/1900 3:00:00 AM instead of the 3:00 hours.
How can I make this show the 3:00 hours ?
I need to make this view because i want to use it to make a cube in the olap, to give a view of the total amount of money paid to the teachers per course by years.
you want to calculate the difference between ENDTIME and START time , you will need a code as follows .
what it do is to take the endtime from first row ad minus it from the 2nd row starttime.
(RID is a populated column( identity )) works as rowid.
SELECT rid,teacher_id,CASE WHEN DATEDIFF (S,
(SELECT ENDTIME FROM #temp DD WHERE D.RID=DD.RID AND D.STN=DD.STN ),
(SELECT TOP(1) STARTTIME FROM #temp DD WHERE D.STN=DD.STN AND DD.RID>D.RID )) >=0
THEN DATEDIFF (S,
(SELECT ENDTIME FROM #tempd DD WHERE D.RID=DD.RID AND D.STN=DD.STN ),
(SELECT TOP(1) STARTTIME FROM #tempdcrd DD WHERE D.STN=DD.STN AND DD.RID>D.RID ))
WHEN DATEDIFF (S,
(SELECT ENDTIME FROM #tempdcrd DD WHERE D.RID=DD.RID AND D.STN=DD.STN ),
(SELECT TOP(1) CALLTIME FROM #tempdcrd DD WHERE D.STN=DD.STN AND DD.RID>D.RID )) IS NULL
END AS T_interval
FROM #TEMP D
GROUP BY ...... (desored set)
order by ......(desired set)
THEN SUM(D.DURATION) ---- gives the last line duration
duration can be datediff (n,starttime- endtime )
END AS T_interval ...... as [coulumn name , alias]
i have selected my entire dataset into a #temp ( temporary table) before i made this .