Spotfire Format Date or DateTime as String

[Jeff Grunschel]

Is there a built-infunction in Spotfire to take a Date or DateTime and format it as a string, based on a format string For example something like,

 

Format([MyDateOrDateTimeColumn], "yyyy-MM-dd HH:mm:ss")Additionally, how does one avoid the regional settings when using the Month or Hour functions The documentation states that the Month function:

Extracts the month from a Date or DateTime column. The underlying data of the new column is an integer between 1 and 12, but regional settings determine the formatted output.

 

If I try to use the Integer result of the Month function, a string is returned. In my case, my computer returns "Aug" for Month(DateTimeNow()). Even if I try to "trick" Spotfire by enclosing it in the Integer() function, "Aug" is still returned.

All of this is because the end user wanted the Dates and DateTimes in specific formats. As a result, I've had to use the following:

 

Year(DocumentProperty("ReportEnd"))

& "-" &

(case

when Month(DocumentProperty("ReportEnd"))=1 then

"01"

when Month(DocumentProperty("ReportEnd"))=2 then

"02"

when Month(DocumentProperty("ReportEnd"))=3 then

"03"

when Month(DocumentProperty("ReportEnd"))=4 then

"04"

when Month(DocumentProperty("ReportEnd"))=5 then

"05"

when Month(DocumentProperty("ReportEnd"))=6 then

"06"

when Month(DocumentProperty("ReportEnd"))=7 then

"07"

when Month(DocumentProperty("ReportEnd"))=8 then

"08"

when Month(DocumentProperty("ReportEnd"))=9 then

"09"

when Month(DocumentProperty("ReportEnd"))=10 then

"10"

when Month(DocumentProperty("ReportEnd"))=11 then

"11"

when Month(DocumentProperty("ReportEnd"))=12 then

"12"

end)

& "-" &

Repeat("0",2 - Len(String(Day(DocumentProperty("ReportEnd"))))) &

Day(DocumentProperty("ReportEnd"))

& " " &

(case

when Hour(DocumentProperty("ReportEnd"))=0 then

"00"

when Hour(DocumentProperty("ReportEnd"))=1 then

"01"

when Hour(DocumentProperty("ReportEnd"))=2 then

"02"

when Hour(DocumentProperty("ReportEnd"))=3 then

"03"

when Hour(DocumentProperty("ReportEnd"))=4 then

"04"

when Hour(DocumentProperty("ReportEnd"))=5 then

"05"

when Hour(DocumentProperty("ReportEnd"))=6 then

"06"

when Hour(DocumentProperty("ReportEnd"))=7 then

"07"

when Hour(DocumentProperty("ReportEnd"))=8 then

"08"

when Hour(DocumentProperty("ReportEnd"))=9 then

"09"

when Hour(DocumentProperty("ReportEnd"))=10 then

"10"

when Hour(DocumentProperty("ReportEnd"))=11 then

"11"

when Hour(DocumentProperty("ReportEnd"))=12 then

"12"

when Hour(DocumentProperty("ReportEnd"))=13 then

"13"

when Hour(DocumentProperty("ReportEnd"))=14 then

"14"

when Hour(DocumentProperty("ReportEnd"))=15 then

"15"

when Hour(DocumentProperty("ReportEnd"))=16 then

"16"

when Hour(DocumentProperty("ReportEnd"))=17 then

"17"

when Hour(DocumentProperty("ReportEnd"))=18 then

"18"

when Hour(DocumentProperty("ReportEnd"))=19 then

"19"

when Hour(DocumentProperty("ReportEnd"))=20 then

"20"

when Hour(DocumentProperty("ReportEnd"))=21 then

"21"

when Hour(DocumentProperty("ReportEnd"))=22 then

"22"

when Hour(DocumentProperty("ReportEnd"))=23 then

"23"

end)

& ":" &

Repeat("0",2 - Len(String(Minute(DocumentProperty("ReportEnd"))))) &

Minute(DocumentProperty("ReportEnd"))

& ":" &

Repeat("0",2 - Len(String(Second(DocumentProperty("ReportEnd"))))) &

Second(DocumentProperty("ReportEnd"))Which gives 2016-08-30 13:45:57, or whatever is the value of the ReportEnd document variable.

Thanks.

[Chen Liang]

Hi,

 

In Spotfire, format and data type are two different concepts. You can change the format of a variable in the Column Properties. Go to Edit menu - Column Properties - Formatting.

 

For the first case, try to change the format of the date column by custom format string "yyyy-MM-dd HH:mm:ss" and then add a new calculatedcolumn like String([DateColumn]).

 

Again, for the second question all you have to do is change format of the month column to General or Number. By default it will return as Aug but when you change the format it will show as number 8. You can of course change it back to Aug by selecting DatePart and choose Type as Month.

[Sean Riley]

Why don't you keep it as a date time but just use a custom formatting, via:

 

Column Properties > your datetime > Formatting > Custom: yyyy-MM-dd hh:mm:ss

 

I know you are taking your input from a document property, but perhaps you can use that formatting to display it in the proper format, depending on where you need to display it.

 

But the short answer is that there isn't a FormatDate() function like you mention. That could potentially be a good enhancement though which you could record on the Ideas Portal. But there are also some existing enhancement ideas around being able to define a global date format that is NOT dependent on local regional settings. I think you would probably want to vote for those as well:

 

http://spotfireideas.tibco.com/ideas/SPF-I-714

http://spotfireideas.tibco.com/ideas/SPF-I-288

 

 

And regarding your month format, when you wrap it in an Integer() it actually is returning "8" but since it is being treated as a month, it is just being displayed as "Aug" still.So if you wanted to do string operations to get a numeric month, in spite of regional display settings, you could use an expression like:

 

String(Integer(Month([MyDateColumn])))

 

This would return "8" and it would not be displayed as "Aug" any longer.

Alternatively:

1). Create a calculated column by making use of the expression - LongInteger(Month([Date Column]))

2). You can also create a calculated column by making use of the expression - Month([Date Column]).

a). Click on the Formatting button present at the right side bottom on the same window and select Number format.

b). If you want to display month in number (two digit) format such as '02', Click on the formatting button>>Custom & put 00 (two zero)

 

Update:

To create a new concatenated a YearMonth value like 201702, you can use an expression like the following, which has a check to ensure the month value is 2 digits (inserts leading 0 when month is 1-9):

 

Concatenate(

Year(DateTimeNow()),

If(Len(String(Integer(Month(DateTimeNow()))))=1,"0"),

String(Integer(Month(DateTimeNow())))

)

[Jeff Grunschel]

Our IT group has blocked the Spotfire Ideas site, so I will check out those links at home.

 

I will remember to use the column/field formats, but I've had to pass DateTime variables to stored procedures within Information Links that required the DateTime to be formatted as specific strings. (I know, poorly written SPs, but it's what I have to work with.)  Wrapping the Month() function in the String() conversion function did the trick!  Much easier than having to use a Case statement.  Thanks!

[Sean Riley]

Nice! Glad that helped simplify your expression a bit : )

[Pratibha K]

@ Sean Riley - Excellent answer!

 

Quick question - I am trying to concatenate year and month extracted from a date column. I used your trick and it worked well. Except that it is displaying the result as 20172 for the date 03 Feb 2017. But, I would like to see the result 201702. Please advise.

 

Concatenate(Year([Date_column]),String(Integer(Month([Date_column]))))

 

Thank you!

 

KSP

[Sean Riley]

Just add a check for the length of the month string and if it is 1 then insert a leading 0, like:

 

Concatenate(
Year(DateTimeNow()),
If(Len(String(Integer(Month(DateTimeNow()))))=1,"0"),
String(Integer(Month(DateTimeNow())))
)

 

 

[Pratibha K]

@Sean Riley - Good solution. Thank you!

[Joseph Huang]

Hi ,

You may try expression as below. Thanks!

TERR_String("output