1 Reply Latest reply on Sep 6, 2013 4:23 PM by E R

    different date representation between csv and extract

    Dan Ward

      I'm not sure how to insert a viz so below is a screenshot of what I'm talking about.

       

      1. I create a small .csv with IDs and dates and connect to it with tableau.

      2. for demonstration purposes I then create a duplicate of the same database and connect to that also. With the second I then extract to a .tde and ensure I am set to use the extract from the second db.

      3. create calculated field for each database =FLOAT([END DATE])

      4. See below the dates extracted from each data source (columns 4 and 5) which are identical and then the float conversion of each (columns 2 and3 ) are off by 2.

       

      So, now I've demonstrated it, this can be restated as when using a non-exported database the float 0 = 12/30/1899 (same as excel) whereas after extract 0 = 1/1/1990 (never seen that before).

       

      This makes date/time manipulation very hard as to take a date and work with 6am on that day you need to add a time (float <1 or date/time) and depending on your original datasource that will either be 1/1/1900 6:00am or 12/30/1899 6:00 am.

       

      I find it hard to believe that I'm not doing something wrong as this seems trivial and someone must have dealt with it years ago.

      Sheet 1.jpg

      Dan