4 Replies Latest reply: May 15, 2014 9:57 AM by SeanM _ RSS

    SQL HostDate to a calculated field. Need help/advice

    Chris J

      I have been trying to figure out how to convert the HostDate (sample: 39234.246539) in one of our SQL databases into a calculated field with a recognizable time/date stamp.

       

      According to my research, SQL timestamps offset in seconds from Jan 1, 1970, at midnight.

       

      I've been playing around with the date functions and I can't seem to get anything to work.

       

      Any thoughts or suggestions?

        • SQL HostDate to a calculated field. Need help/advice
          Grant Perkins

          Hi Chris,

           

          Glad you have V9!

           

          If you look in the sample User Defined Functions model that comes with V9 you should find two functions that will either do the job for you or get you a close formula for defining your own UDF.

           

          The UDF model should be available as a default set of UDFs to be imported for any model you are creating.

           

          Subselect on the list by filtering for "unix" in the list and you should get 2 results, on to change a unix time number to a date and the other to change a date to a unix time number.

           

          Unix time number is based on a start date of

          01-01-1970.

           

          HTH.

           

           

          Grant

          • SQL HostDate to a calculated field. Need help/advice
            Chris J

            Grant,

             

            Thanks for the input. I forgot to mention that I tried using the Unix date conversion functions and I keep getting 12/31/1969.

             

            calculated date

            1969-12-31 1:06:06 PM

             

            I'm sure I probably need to add something in the calculation/function. Actually, all 56,000+ records are giving me that same date, different times, though.

            • SQL HostDate to a calculated field. Need help/advice
              Grant Perkins

              Chris,

               

              What date are you expecting to see?

               

              I suspect that your test number is not the expected value in seconds.

               

              Also that the formula should be + rather than - unless the seconds value is expected to be presented as a negative.

               

              Whatever your numeric value is will require a change to the formula but the basic principle is explained therein. Try converting a date to Unix time to see what you get.

               

              My quick research suggests there are alternative to the 1970 concept and that the extracted date/time might appear in one of a number of formats. But I am no expert.

               

              See if any of this looks familiar.

               

              http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html[/URL]

               

              I suspect that the SQL users expect to just convert to a recognisable format before presenting the data to the world. Is that an option for you too?

               

              I'm not sure how much this helps you but to the casual reader the comments in various discussion groups certainly help to explain why applications can be problematic from time to time.

               

              Grant