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.