Yes, the dateparse does not work in SQL . Thanks for the reply.
1 of 1 people found this helpful
You can try below approach:
Date = INT(LEFT(STR([Datetime]),8))
Time = INT(RIGHT(STR([Datetime]),6))
Hour = INT(LEFT(STR([Time]),2))
Minute = INT(RIGHT(LEFT(STR([Time]),4),2))
Second = INT(Right(STR([Time]),2))
Maketime = MAKETIME([Hour],[Minute],[Second])
Arrange the fields as shown below:
Yes, if that the case, you need to create each year, each month's starting point with 5 digits numeric value.like "41734", unfortunately...
The link I provided was not considering leap year, then actually the formula needs to be longer. But logic itself is quite straight forward, just troublesome.
Thank you for the reply. The date field looks good but the make time function is not working. The error says "Unknown Function MakeTime Called".
If dateparse is not working, makedate does not work neither.
This may not work for you, but maybe you can try some version of DATEADD:
You may need to modify, but it could be something along the lines of:
DATEADD('minute', INT(MID([Date Sec],11,2)),
DATEADD('hour', INT(MID([Date Sec],9,2)),
DATEADD('day', (INT(MID([Date Sec],7,2))),
DATEADD('month', INT(MID([Date Sec],5,2)) - 1, // note the -1 needed here
Please see workbook v10.3 attached in the Forum Thread.
282973dateadd.twbx 20.5 KB
2 of 2 people found this helpful
Which version of Tableau are you using??
One of the reason of function not working is , may be you are using "live data connection" and you source database doesn't have any equivalent function to MAKETIME.
You can change the connection to extract and then use the formula.
But, if you have to use only live connection, then there is a workaround, we can display the value as time,but it will be number only,we can change the format to display it as time.
1. Seconds = [Minute]*60 + [Hour]*3600+[Second]
2. New Time =
IIF([Seconds] % 60 == 60,0,[Seconds] % 60)
+ IIF(INT([Seconds]/60) %60 == 60, 0, INT([Seconds]/60) %60) * 100
+ IIF(INT([Seconds]/3600) % 24 == 0, 0, INT([Seconds]/3600) % 24) * 10000
+ INT([Seconds]/86400) * 1000000
Change the format of New time to as shown below:
Thank you for the reply. The attached worksheet was helpful.