11 Replies Latest reply on Sep 29, 2017 5:48 PM by Allen Kei

# Converting string to Date time format

Hi,

I am looking to convert string like 1160510204551000 to datetime format when working in Tableau. Can you let me know how this can be done? Thanks in advance,

year is 2 and third digit

month is represented by 4th and fifth digit

date is  6 and seventh digit

hour is 8th and ninth digit

Mins is 10 and 11th digit

Secs is 12 and 13th digit

• ###### 1. Re: Converting string to Date time format

Date should be straightforward with MAKEDATE. Try:

MAKEDATE('20' + MID([Field],2,2),MID([Field],4,2),MID([Field],6,2))

Time is similar with MAKETIME:

MAKETIME(MID([Field],8,2),MID([Field],10,2),MID([Field],12,2))

However combiing them is slightly more complex. The time needs to be converted to seconds.

You can extract out the hours, minutes and seconds separately to make reading easier. In this example I'll put them in one formula, which should give you the number of seconds:

INT(MID([Field],8,2))/3600 + INT(MID([Field],10,2))/60 + INT(MID([Field],12,2))

Then you can do  DATEADD to get the full date time: DATEADD('second',[SecondsField],[MakedateField]) or (more difficult to read):

DATEADD('second',INT(MID([Field],8,2))/3600 + INT(MID([Field],10,2))/60 + INT(MID([Field],12,2)),MAKEDATE('20' + MID([Field],2,2),MID([Field],4,2),MID([Field],6,2)))

I strongly recommend you make these formulas separately as I haven't tested and there could be problems with the brackets and syntax.

• ###### 2. Re: Converting string to Date time format

Here you go.

[Conversion]

dateparse("yyyy/MM/dd","20"+mid([Data],2,2)+"/"+mid([Data],4,2)+"/"+mid([Data],6,2))

+float(mid([Data],8,2))/24

+float(mid([Data],10,2))/(24*60)

+float(mid([Data],12,2))/(24*60*60)

Thanks,

Shin

• ###### 3. Re: Converting string to Date time format

FIRST DIGIT CONSTANT

If prefix is a constant (1), this might also be a way:

DATEPARSE( "1yyMMddhhmmss", [DateStr] )

In case 1 was a formatting code like y, s, M, etc, you could enclose it with singles quotes.

Of course, you could do this anyway

DATEPARSE( "'1'yyMMddhhmmss", [DateStr] )

IGNORE TEXT

I don't know if DATEPARSE has any formatting code for ignoring string positions with varying content, but interestingly, it is possible to use other codes for this purpose, such as:

day of week in month - 2 (2nd Wed in July)

W  week of month - 2

The examples below use F where text should be ignored.

FIRST DIGIT VARIES

Even if the first digit isn't a constant, this seems to work too:

DATEPARSE( "FyyMMddhhmmss", [DateStr] )

TIME

This formula might work for TIME only:

DATEPARSE( "FFFFFFFhhmmss", [DateStr] )

• ###### 4. Re: Converting string to Date time format

Be aware that with two digits for year, DATEPARSE chooses a suitable century itself.

Currently, the break is at 30/31. I don't know when this will be changed to something else.

If year is 30, it will be 2030.

If year is 31, it will be 1931.

• ###### 5. Re: Converting string to Date time format

ARE F and W safe to use?

I haven't been able to make them do anything, and thus currently of the opinion that they are safe to use as code for text to ignore. But of course I cannot and will not give any guarantees, so whoever uses them uses them at their own risk

Why it is like this, I don't know. It could be because it isn't currently supported as noted  here.

If this is the case, errors might arise if F and W suddenly are supported in the future.

TESTS

All these calculations return the same result ( 2016-02-01 ):

DATEPARSE( "yyyy-MM-F", "2016-02-1" )

DATEPARSE( "yyyy-MM-F", "2016-02-2" )

DATEPARSE( "yyyy-MM-F", "2016-02-3" )

DATEPARSE( "yyyy-MM-F", "2016-02-4" )

DATEPARSE( "yyyy-MM-W", "2016-02-1" )

DATEPARSE( "yyyy-MM-W", "2016-02-2" )

DATEPARSE( "yyyy-MM-W", "2016-02-3" )

DATEPARSE( "yyyy-MM-W", "2016-02-4" )

F  day of week in month - 2 (2nd Wed in July)

W  week of month - 2

Formatting Dates and Times - ICU User Guide

• ###### 6. Re: Converting string to Date time format

Thanks all for the response. I am making live connection to DB2 database in my workbook. Dateparse function is not showing up with Live connection to db2. I am getting below error.

Unknown function Dateparse called.  Can you let me know if I can use some other function to convert string to Datetime?

• ###### 7. Re: Converting string to Date time format

I am making live connection to DB2 database in my workbook. Dateparse function is not showing up with Live connection to db2. I am getting below error.

DATEPARSE isn't available for DB2 as documented here:

This function is available for non-legacy Microsoft Excel and text file connections, MySQL, Oracle, PostgreSQL, and Tableau data extract data sources. Some formats may not be available for all data sources.  Onlinehelp > Date Functions

• ###### 8. Re: Converting string to Date time format

Can you let me know if I can use some other function to convert string to Datetime?

Could you check if this works:

RAWSQL_DATETIME("TIMESTAMP_FORMAT( SUBSTR( %1, 2, 12 ), 'YYMMDDHH24MISS' )", FIELDNAME )

If it doesn't and you have a tool to query DB2, could you check if this works:

TIMESTAMP_FORMAT( SUBSTR( FIELDNAME, 2, 12 ), 'YYMMDDHH24MISS' )

You could use RR instead of YY.

Ps.  I have never used DB2 and therefore base my knowledge on this:

IBM Knowledge Center : TIMESTAMP_FORMAT

IBM Knowledge Center : SUBSTR

• ###### 9. Re: Converting string to Date time format

I created  a new calculated column with below formula and this worked with Live connection to DB2 to convert string to datetime.

DATETIME(STR(20)+MID([Timestamp],2,2)+"-"+MID([Timestamp],4,2)+"-"+MID([Timestamp],6,2)+"-"+MID([Timestamp],8,2)+"."+MID([Timestamp],10,2)+"."+MID([Timestamp],12,2))

• ###### 10. Re: Converting string to Date time format

Could you check if this calculation also works in Tableau?

RAWSQL_DATETIME( "TIMESTAMP_FORMAT( SUBSTR( %1, 2, 12 ), 'YYMMDDHH24MISS' )", [Timestamp] )