1 2 Previous Next 16 Replies Latest reply on Nov 22, 2017 3:03 PM by Irena Sagan

    Re: How Do I Strip the Date off a Date/Time Field?

    Satish Goud Kasaram

      Hi

       

      I want to get Just the time of the Date/Time field in the format of "H:MM:SS" .... Any help would be highly appretiated.

       

      Thank You
      Satish

        • 1. Re: How Do I Strip the Date off a Date/Time Field?
          Parthasarathi Sengodan

          hi satish,

           

          you can do it with the help of a calculated field.

           

          ex:

          datepart('hour',[your datetime field])+':'+datepart('minute',[your datetime field])+':'+datepart('second',[your datetime field])

           

          hope it helps

           

          regards,

          parthasarathi

          3 of 3 people found this helpful
          • 2. Re: How Do I Strip the Date off a Date/Time Field?
            Satish Goud Kasaram

            Parthasarathi

             

            I tried this and it says cant add Integer and String....

             

            Also i have another code which works perfect but i am only missing "seconds" part in it..... if you can help me with that....

             

            IF DATEPART('hour',[YourDateTimeField]) = 0 THEN '12'

            ELSEIF DATEPART('hour',[YourDateTimeField])<10 THEN STR(DATEPART('hour',[YourDateTimeField]))

            ELSEIF DATEPART('hour',[YourDateTimeField])>12 THEN STR(DATEPART('hour',[YourDateTimeField])-12)

            ELSE STR(DATEPART('hour',[YourDateTimeField])) END //hour

            + ':' +

            IF DATEPART('minute',[YourDateTimeField])<10 THEN '0'+STR(DATEPART('minute',[YourDateTimeField]))

            ELSE STR(DATEPART('minute',[YourDateTimeField])) END //minute

            +' '+

            IF DATEPART('hour',[YourDateTimeField])>=12 THEN 'PM' ELSE 'AM' END //AM or PM

             

            Thank You
            Satish

            1 of 1 people found this helpful
            • 3. Re: How Do I Strip the Date off a Date/Time Field?
              Parthasarathi Sengodan

              Satish

               

              there is a date function available called Dateparse().Unfortunately it is available only with certain connections.lets hope that you use any one of those connections to load your data.the formats for the conversion are provided in the following link.

              Understanding the DATEPARSE Function | Tableau Software

               

              as for the working code you mentioned ,you are just missing the seconds part

               

              IF DATEPART('hour',[YourDateTimeField]) = 0 THEN '12'

              ELSEIF DATEPART('hour',[YourDateTimeField])<10 THEN STR(DATEPART('hour',[YourDateTimeField]))

              ELSEIF DATEPART('hour',[YourDateTimeField])>12 THEN STR(DATEPART('hour',[YourDateTimeField])-12)

              ELSE STR(DATEPART('hour',[YourDateTimeField])) END //hour

              + ':' +

              IF DATEPART('minute',[YourDateTimeField])<10 THEN '0'+STR(DATEPART('minute',[YourDateTimeField]))

              ELSE STR(DATEPART('minute',[YourDateTimeField])) END //minute

              +':'+

              IF DATEPART('second',[YourDateTimeField])<10 THEN '0'+STR(DATEPART('second',[YourDateTimeField]))

              ELSE STR(DATEPART('second',[YourDateTimeField])) END //second

              +' '+

              IF DATEPART('hour',[YourDateTimeField])>=12 THEN 'PM' ELSE 'AM' END //AM or PM.

               

              unfortunately my trial version ended and i cant check this code out with public unless i have mock data.if you are to use tableau public ,the dateparse() function will be available.be sure to give it a try.

               

              regards

              parthasarathi 

              3 of 3 people found this helpful
              • 4. Re: How Do I Strip the Date off a Date/Time Field?
                Satish Goud Kasaram

                Yes Parthasaradhi

                 

                I did try that and it worked ..... Thanks neways....

                 

                Thank You
                Satish

                • 5. Re: How Do I Strip the Date off a Date/Time Field?
                  stephanie.sims

                  I am actually hoping to strip the time off of my Date Time value. Is there a formula I can use for that?

                  • 6. Re: How Do I Strip the Date off a Date/Time Field?
                    Joe Oppelt

                    Stephanie:  Use DATE([date-time-variable-field])

                     

                    It just grabs the date portion.

                    • 7. Re: How Do I Strip the Date off a Date/Time Field?
                      stephanie.sims

                      Thank you Joe!! Using DATE() worked for me! I appreciate it!

                      • 8. Re: How Do I Strip the Date off a Date/Time Field?
                        Steph Dietzel

                        Hi Satish,

                         

                        I just had a similar scenario where I just want the time to display. (For me, it didn't matter that the underlying data was a datetime). I was able to change the formatting options of the field and get it to look how I wanted, using the custom format string "h:nn:ss AMPM".

                         

                        You can set it as the default for a field like this:

                         

                        7-16-2015 9-15-20 AM.jpg

                        7-16-2015 9-14-50 AM.jpg

                         

                        Or for just one pill like this:

                        7-16-2015 9-15-49 AM.jpg7-16-2015 9-16-14 AM.jpg

                         

                        Thanks,

                        Steph

                        3 of 3 people found this helpful
                        • 9. Re: How Do I Strip the Date off a Date/Time Field?
                          Joseph Pantuso

                          I'm a little late to the party, but here are my solutions in Version 9.0 (and this link to explain the built-in date/time functions).

                           

                          If you have a field with date and time (named [Date Time Interval] below) and you're using an extract, you can use the MAKETIME function to create a field that is recognized as time (for further calculations or displaying in particular ways):

                           

                          MAKETIME(

                                  DATEPART('hour',[Date Time Interval]) // Get hours from your date-time field//

                              ,   DATEPART('minute',[Date Time Interval]) // Get minutes from your date-time field. Put a 0 here to leave it out.//

                              ,   DATEPART('second',[Date Time Interval]) // Get seconds from your date-time field. Put a 0 here to leave it out. //

                              )

                           

                          If you'd prefer to have a string of characters that looks like a timestamp in hh:nn:ss format (without actually having any numerical value), you can do the following:

                           

                          IF LEN(STR(DATEPART('hour',[Date Time Interval])))=1 // If the 'hour' part of the timestamp is 0-9... //

                              THEN "0"+STR(DATEPART('hour',[Date Time Interval])) // Turn it into 00-09, so your hours are always two digits //

                              ELSE STR(DATEPART('hour',[Date Time Interval])) // Otherwise, it's cool: 10-24 is fine //

                          END

                          + ":" + // Add a colon between the two parts //

                          IF LEN(STR(DATEPART('minute',[Date Time Interval])))=1 // Like above, if the 'minute' part of the timestamp is 0-9... //

                              THEN "0"+STR(DATEPART('minute',[Date Time Interval])) // Turn it into 00-09, so your minutes are always two digits //

                              ELSE STR(DATEPART('minute',[Date Time Interval])) // Otherwise, it's cool: 10-59 is fine //

                          END

                          + ":" + // Add another colon between the two parts //

                          IF LEN(STR(DATEPART('second',[Date Time Interval])))=1 // Like above, if the 'second' part of the timestamp is 0-9... //

                              THEN "0"+STR(DATEPART('second',[Date Time Interval])) // Turn it into 00-09, so your seconds are always two digits//

                              ELSE STR(DATEPART('second',[Date Time Interval])) // Otherwise, it's cool: 10-59 is fine //

                          END

                           

                           

                          To go the opposite direction (like Stephanie wanted), you can use the following:

                           

                          DATETRUNC('day',[Date Time Interval])

                           

                          DATETRUNC will clip off anything after the 'day' part of your field, removing those pesky hours, minutes, and seconds. You can change the level of precision you want to truncate, making it useful to get down to months, minutes, or more.

                           

                          And of course, you've get a few more options that people have suggested above. Hope that helps!

                          1 of 1 people found this helpful
                          • 10. Re: How Do I Strip the Date off a Date/Time Field?
                            Ganesh S

                            Hi Joe,

                             

                            It just grabs the date field alone when we doing DATE([date field]), everything was perfect in Tableau Desktop when we upload this to server there the time part gets appeared,

                             

                            any solution for this? My source database is Oracle. Jonathan Drummey

                            • 11. Re: How Do I Strip the Date off a Date/Time Field?
                              Jonathan Drummey

                              Internally dates are generally stored as floating point numbers where the decimal portion is a fractional day and the the whole number portion is the number of days since an epoch date. So to strip out dates & times we can use math functions:

                               

                              Try DATE(INT([your field])) to turn a datetime into just the date portion. To turn a datetime into just a time, use DATETIME(FLOAT([your field])-INT([your field])) then format it to not show the 12/30/1899 or whatever your epoch date is. (Note that Tableau doesn't currently truly support durations, so you have to be careful when adding times across fields, especially when using Tableau data extracts that can have an epoch date that is different from your underlying data source).

                               

                              Jonathan

                              • 12. Re: How Do I Strip the Date off a Date/Time Field?
                                Joe Oppelt

                                Ganesh -- A disparity in behavior like that should be treated as a bug.

                                 

                                Create a packaged workbook with a tiny example.  If it works in desktop and fails in server, that should go to Support.

                                • 13. Re: How Do I Strip the Date off a Date/Time Field?
                                  Elbert Nieves

                                  Thanks, Steph. Exactly the answer I was looking for to solve this.

                                  • 14. Re: How Do I Strip the Date off a Date/Time Field?
                                    Atul Divekar

                                    Hello Satish,

                                     

                                    Kindly check Date Functions

                                     

                                    MAKEDATETIME(date, time)

                                    Returns a datetime that combines a date and a time. The date can be a date, datetime, or a string type. The time must be a datetime. This function is available only for MySQL connections.

                                    Examples

                                    MAKEDATETIME("1899-12-30", #07:59:00#) = #12/30/1899 7:59:00 AM#

                                    MAKEDATETIME([Date], [Time]) = #1/1/2001 6:00:00 AM#

                                    MAKETIME(hour, minute, second)

                                    Returns a date value constructed from the specified hour, minute, and second.

                                    Available for Tableau Data Extracts. Check for availability in other data sources.

                                    Example

                                    MAKETIME(14, 52, 40) = #14:52:40#

                                     

                                    Regards,

                                    Atul Divekar

                                    BI Tableau Consultant

                                    1 2 Previous Next