SQL Server 2005 Date/Time Problem , Please Help

[roxys]

Hello,

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.

#1 Issue

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 ?????

#2 Issue

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.

Thanks

[Difou]

Hi,

You should give a look to the SQL function DATEDIFF

http://msdn.microsoft.com/en-us/library/ms189794(SQL.90).aspx

Because you can't substract date directly in an expression.

Bie

[sknake]

You can work with dates directly.

substract date directly in an expression.
Bie

The integral part of the DateTime value is the number of days since the first day defined for the datatype, and the decimal portion is the time.

Declare @dt DateTime
Set @dt = GetDate()

Select 
@dt As RightNow,
@dt+1 As RightNowPlus24Hours,
Cast(Floor(Cast(@dt as float)) as datetime) as HackOffTimePortion

For the purposes of the date formatting that should be done on the application layer and not the database layer. As mentioned earlier DateDiff(), DateName(), DatePart(), DateAdd() are all very useful functions when working with dates.