dcsimg
Results 1 to 16 of 16

Thread: I'm having a problem with midnight

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    I'm having a problem with midnight

    I'm working on a form in our application where an employee enters hours worked for each day.
    The data is written out to a table called Hours, and one pair of fields for example is TimeIn and TimeOut. They are specified in SQL Server as datatype datetime and do not allow nulls.
    If the data is not filled in, therefore, the default value is 1900-01-01 00:00:00.000
    It seems I cannot represent midnight as 24:00. It wants to be represented as 00:00.
    So now I have a problem discerning does 00:00 mean they worked until midnight or they did not work at all?
    I guess the solution would be to allow nulls in these columns. Is there an alternative? This is not a new table and it has people's hours worked for at least the past 15 years.
    ...meanwhile
    So my boss just poked her head in and when she said I didn't seem happy I showed her an example record.
    She said how it really should be is that instead of one long record for each day and filling in or leaving blank time such as regular in/out, overtime in/out, paid time off in /out, we redesign the whole structure and an employee will have 3 records if he's worked regular hours and had some paid time off and also worked overtime all in the same day! That will be nice, but I'm not going to do it in an afternoon.
    So in the meantime, do I have any alternatives? And can I ask the rhetorical question why midnight isn't represented as 24:00? Or it could be...if I used some other calendar?
    In Visual Studio in the debugger (I am using C#) if I say ? Convert.ToDateTime("24:00") it comes back with "The DateTime represented by the string is not supported in calendar System.Globalization.GregorianCalendar."
    Thanks and sorry for the stream of consciousness
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,146

    Re: I'm having a problem with midnight

    If it existed, 24:00 on one day would be exactly the same as 0:00 the next day. As such, there is no reason for both to exist.

    Presumably when values are stored, you store the date as well as the time, so you should be able to check whether the year is 1900, and therefore treat that value as an equivalent of null.

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    Re: I'm having a problem with midnight

    Quote Originally Posted by si_the_geek View Post
    ...Presumably when values are stored, you store the date as well as the time...
    Nope, presumably I do not. There's a whole separate column called Date and that's the day of the week that the person worked. Then all the other columns, even though they are DateTime types, we only store the time. For example, 1899-12-30 20:00:00.000. Now you see what I am up against.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,146

    Re: I'm having a problem with midnight

    OK... so if the Date column has a proper value, does that mean that the times are all trustworthy?

    It might help to see some sample data.

  5. #5
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,177

    Re: I'm having a problem with midnight

    Since she said "redesign the whole structure" you should take her up on that and do it right. Having separate columns for date and time when they need to be related will cause you nothing but headaches. I don't see any reason why you would ever need date and time not to be related in this application.

    Midnight (12 AM) is officially 00:00 of the NEXT day.

  6. #6

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    Re: I'm having a problem with midnight

    So in all fairness to the programmer who designed this, he probably thought it was redundant to specify the date in every column where we save time. In other words, you have Date once and let's say it's 3/28/19 and then in all the flavors of hours you can get paid for - regular, overtime, sick time - you only have to say the time started and time ended because Date has the date (I'm guessing but that seems likely to me).

    I *will* take her up on it. I was just hoping to be able to do something quickly in the interim. Designing a new table, copying over all the data especially when it's one row getting turned into many, changing all the stored procedures and the xsd files in my front-end code, that will take a while and we will probably postpone it. I was hoping to have a solution in the meantime.

    A couple other things I should mention. Users are only allowed to enter time in 15-minute increments. Users currently have never been able to enter midnight (this is a conversion of a VB6 application). So if they worked 10:00 pm to midnight, they might enter 9:45 pm to 11:45 pm (messy, I know, which is why I wanted to improve this).

    @si_the_geek - yes times are trustworthy. I am not sure why you were distrusting. Also regarding showing you data, I have been unable to upload a jpg in this forum the last few times I tried. Is this just me???
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,908

    Re: I'm having a problem with midnight

    There is a simple reason, why "24:00" doesn't exist.
    It's historical and has to do how the hour of a day was announced in olden times.
    the phrase "it's One O'Clock now" (--> like 01:00 AM) actually translates to "one hour since the day began has PASSED!"
    An hour has 60 Minutes.
    When does the first minute begin? exactly, at 00
    Meaning: "00:00" is actually the beginning of the first minute of the first hour of a day.
    and "00:59" is the beginning of the last minute of the first hour.
    European Soccer-Results in a Newspaper show it the best.
    "And Team Red scored a Goal at 34:45" (34 minutes and 45 seconds), thepapers often write "they scored in the 35th minute" because at 34:45 the 35th minute was still running
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,908

    Re: I'm having a problem with midnight

    Quote Originally Posted by MMock View Post
    Nope, presumably I do not. There's a whole separate column called Date and that's the day of the week that the person worked. Then all the other columns, even though they are DateTime types, we only store the time. For example, 1899-12-30 20:00:00.000. Now you see what I am up against.
    Errr? Why would you do that (resp. don't do that)?
    A DateTime is basically nothing else than a Numeric Value of Type Unsigned Double, the Integer-Part representing the Days since your Base-Day (Day 0 being what? Jan 1st, 1900?), and the fractional part representing the time.
    A value 2.75 is Jan 3rd, 1900 - 6PM in my example
    If you store the Date with the time, there is no difference in memory-consumption: it's still the memory of a double 8 Bytes, and the others above told you all the reasons why you should do it.
    Last edited by Zvoni; Mar 28th, 2019 at 12:33 PM.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  9. #9
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,177

    Re: I'm having a problem with midnight

    Quote Originally Posted by MMock View Post
    So in all fairness to the programmer who designed this, he probably thought it was redundant to specify the date in every column where we save time.
    They are specified in SQL Server as datatype datetime
    Why wouldn't he have used Date and Time data types if that was the case? You must have a date anyway in DateTime data type so may as well enter it, which makes a programmer's life easier all around. You can easily span 2 days then if needed. At least it's not as bad as putting dates/times in string columns!

  10. #10
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,446

    Re: I'm having a problem with midnight

    Quote Originally Posted by topshot View Post
    Why wouldn't he have used Date and Time data types if that was the case? You must have a date anyway in DateTime data type so may as well enter it, which makes a programmer's life easier all around. You can easily span 2 days then if needed. At least it's not as bad as putting dates/times in string columns!
    The Date type in SQL server is a recent addition DateTime has been around forever.... yeah, I agree it sounds like it was a bonehead decision, but 1) hindsight is 20/20. And 2) it's not NMock's fault, so no sense in gettting all up in her grill about it. Besides, even she agrees that is was a stupid move. Unfortunately she's the one that has to inherit the problem... we've all been there. I'm there now too... I'm inheriting some C# code that will need to be converted to Java for a service.... I looked at it today....there's some ........ rather creative coding going on... a lot of which shouldin't be happening. Now I have to figure out how to unwind it and work it into a Java service project. Fortunately it's 98% data structure, but it's so full of redundant structures, I'm not sure where to begin. (at the top I guess).

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,177

    Re: I'm having a problem with midnight

    I hope MMock wasn't thinking I was attacking her. We've all most likely had to deal with poor decisions of the past (whether our own or someone else's) as my last sentence alluded to in my current job. Perhaps DB2 just didn't have date/time data types 30 years ago.

  12. #12

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    Re: I'm having a problem with midnight

    No, didn't feel that way at all and I have been meaning to get back to this but let's face it, it's not a fun problem so I think I subconsciously blocked it out.
    I've also been adhering to the adage "if it ain't broke don't fix it". It isn't broken and users are used to it; it could just work better.
    I'll be back next week sometime. Thanks.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  13. #13
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,348

    Re: I'm having a problem with midnight

    If the times are valid could you take the date from the date column the time portion from the time column and put them back together as a datetime value?
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  14. #14

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    Re: I'm having a problem with midnight

    No, didn't feel that way at all and I have been meaning to get back to this but let's face it, it's not a fun problem so I think I subconsciously blocked it out.
    I've also been adhering to the adage "if it ain't broke don't fix it". It isn't broken and users are used to it; it could just work better.
    I'll be back next week sometime. Thanks.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  15. #15

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    Re: I'm having a problem with midnight

    Sorry, I refreshed and vbForums reposted.
    Usually it's only my smart phone that makes me look like an idiot.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  16. #16
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,039

    Re: I'm having a problem with midnight

    Ho

    see if this will help, you would have to seperate the DateTime

    this is .Net, I don't know how old the Program is but in VB6 you can do the exact same

    Code:
    Public Class Form1
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            MsgBox(ConvertTimeStringToMin("00:00") & " minutes")
            MsgBox(ConvertTimeStringToMin("06:13") & " minutes")
            MsgBox(ConvertTimeStringToMin("14:18") & " minutes")
            MsgBox(ConvertTimeStringToMin("24:00") & " minutes")
    
    
            MsgBox(ConvertTimeStringToMin("24:00") - ConvertTimeStringToMin("00:00") & " minutes worked")
        End Sub
    
        Private Function ConvertTimeStringToMin(ByVal t As String) As Integer
            Dim splitString() As String = t.Split(":"c)
            Return ((CInt(splitString(0)) * 60) + CInt(splitString(1)))
        End Function
    End Class
    regards
    Chris
    Last edited by ChrisE; Apr 7th, 2019 at 02:01 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width