Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2008
    Posts
    8

    Unanswered: SQL Server 2005 Date/Time Problem, Please Help

    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

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    All Access does is use smoke and mirrors. It doesn't actually format the data in the table, it formats the output that you see. SQL Server is showing you what the data really is under the covers.

    If you are displaying this in Access, just use the Access Format() function. The best practice advice is to perform this formatting at the last possible stage (i.e. only as you display it). You can convert these dates to strings in SQL but then they are just that - strings.
    Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2009
    Posts
    262
    Do you want me to format your output.

    or do you want me to explain how to prevent it before insert .


    i can do the query for you , but i cannot explain how to prevent it from happening .

  4. #4
    Join Date
    Aug 2009
    Posts
    262

    for solution #2

    for your 2nd question here is the reply .

    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
    THEN SUM(D.DURATION)
    ELSE 0
    END AS T_interval
    INTO #TEMP_2
    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 .

  5. #5
    Join Date
    Aug 2009
    Posts
    262

    for reason 1 .

    if you want to convert it at the end of sql server

    select right (convert (varchar, getdate(), 0), 7)
    for output in HH:MMS ( hours , min , seconds) i.e ( 6:00 pm )

    convert(varchar, yourcolumn , 106) for output in DD MON YY
    i.e 11-oct-2009



    I am not the authority , there are masters of this field who knows better then me ,

    I am just a person who have been pushed from oracle to sql server by management

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •