Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: storing hours

  1. #1
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227

    Unanswered: storing hours

    I use SQL 2K with an Access 2000 project as front end. I have four date fields in a table. The first one should contain a date (eg 20th of February), the second one a starting hour (eg 8:00h), the third one the finishing hour (eg 10:00h) and the fourth should calculate the difference between the second and third column.
    So I created four datetime fields. Storing a date in the first column is no problem, but storing only hours doesn't seem to do what I want. When I enter 8:00 from within the Access, it is stored 1/1/1900 8:00 in SQL Server. How can I make sure that the hours are saved with the date of the second column, so either as 8:00 without further information or as eg 20-2-2004 8:00?
    Johan

  2. #2
    Join Date
    Mar 2004
    Location
    Ohio
    Posts
    16
    Did you try to Format your field in the Access form to Medium Time?

  3. #3
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    we tried from normal date format to small time format, but there is no difference in how SQL server stores the dates.
    Johan

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Why are you using the DateTime data type for the hour columns? I'd consider using tinyint.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  5. #5
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    how do you want to store 8:15h? You can also store it as a float and then calculate, tranform, ...
    Johan

  6. #6
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by jora
    how do you want to store 8:15h? You can also store it as a float and then calculate, tranform, ...
    He was just talking about the hour, not about minutes.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Understand the way the date is physically being stored in sql server...

    It's store as decimal(8,4)

    There will always have to be a date and time component...

    Yukon is going to change that though...

    And I disagree with the int suggestion...takes all of the date function out of play...

    Code:
    DECLARE   @x datetime, @y datetime, @z datetime
     SELECT	  @x = '03/17/2004', @y = '9:00', @z = '17:00' 
     SELECT   CONVERT(varchar(10),@x,101) AS StPatricksDay
    	, CONVERT(varchar,@y,108) AS StartWork
    	, CONVERT(varchar,@z,108) AS EndWork
    	, DATEDIFF(hh,@y,@z) AS HoursWithoutAMargarita
    but that's just me....

    MOO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Mar 2004
    Location
    Ohio
    Posts
    16
    Does your code also correctly calculate the amout of time second shift would have to wait before getting drunk?

  9. #9
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    Brett:
    This is what I know too, and unfortunately knowing this does not solve my problem. So the the "solution" that I found until now is to enter a date in the second column and hours in the second and third. Aftwards, using a trigger to add the day part of the second column to the two hours columns. If someone has a quicker/simpler solution ... yes please.

    JKassil:
    Calculation of the fourth column is ok, but is stored, for example:
    24/02/2004 8:00h 10:00h --> result for the difference 01/01/1900 2:00h
    Last edited by jora; 03-17-04 at 11:01.
    Johan

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Damn...ya know, reading the question can sometimes be a big help


    is this what you want?

    Code:
    DECLARE   @x datetime, @y datetime, @z datetime
     SELECT	  @x = '03/17/2004', @y = '9:00', @z = '17:00' 
     SELECT   CONVERT(datetime,CONVERT(varchar(10),@x,101)+' '+CONVERT(varchar,@y,108)) AS WhatyouReallyWant
    I guess in acces you juest replace the lcal variables wit the control...

    Yes?

    No?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    Yes, about this result is what I had in mind. Easier was not possible, like something with data types.
    Johan

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why not just use 1 field for both?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Mar 2004
    Location
    Ohio
    Posts
    16
    Brett:
    For me, I used additional start and end time float fields and calculated hours as the date fields changed.

    'vba snippet in my Access form...
    'jobend (datetime)
    'jobstart (datetime)
    'endjobtime (float)
    'jobtime (float)

    Private Sub jobend_AfterUpdate()
    If (DatePart("h", jobend) + (DatePart("n", jobend) / 60)) - (DatePart("h", jobstart) + (DatePart("n", jobstart) / 60)) >= 0 Then
    [endjobtime] = (DatePart("h", jobend) + (DatePart("n", jobend) / 60))
    Else
    [endjobtime] = (DatePart("h", jobend) + 24# + (DatePart("n", jobend) / 60))
    End If
    End Sub


    Not very elegant, but it gets me the desired results without adding tons of processing time calculating hours for an ETL procedure for the warehouse.

    I apologize for getting off track, it's just I too would like a better solution

  14. #14
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    well, it's for our eployees time records. We are a data-entry company and entering a date twice is considered a loss of time. So one date, one start hour, one finish hour and one calculation it is...
    Johan

  15. #15
    Join Date
    Mar 2004
    Location
    Ohio
    Posts
    16
    Exactly, ours is for tracking production hours.

Posting Permissions

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