geting the year out of dates

good morning,

i got a data set where the date is written like this: "Jan 16, 2011". now i want to group the data by years but in the group header there should only be shown the year and not the first date entry for the year.
i tried to use .substr() in the expression in the group but apparently the basic java scripts functions won't work in here.

hope you are able to help me.
thanks

Find more posts tagged with

Comments

Hans_vd

Hi newbird,

If you select the field, you can go to the properties tab -> Format DateTime and choose custom. You can then enter yyyy as the format.

Hope this helps
Hans

newbird

ahh, okay, thought i had to define the style in the expression.

thank you, worked very well

Hans_vd

You can do it in the expression as well.
Though you shouldn't be using substr on a date field, as it expects a String as input.

I suppose something like this should work:
BirtDateTime.year(your_field)

The output of this function is String, so don't forget to change the data type of your report field to String then.

Regards
Hans

jain

Hi, I have period field in the dataset with the value "Jan 2012", I have tried using substr() to filter only the year in the fetch method, but its showing type error "substr is not a function".

I want only the year in fetch method. could anyone provide a solution?

Clement Wong

Jain, 
 
What (data) type is your period field? If you go to your Data Set and bring up the Edit Data Set dialog, and select the Output Columns to look at the Type column, what does it show? 
 
The substr() will only work for a String Type. If your column was a String, in the OnFetch method (assuming format of mmm yyyy), you could do: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>row["yourDateColumn"] = row["yourDateColumn"].substr(4,4);</pre>
 
If your column is a date, you might want to convert to the string and then use the substring function. 
 
 
Also, instead of the onFetch method, you can push the formatting to the database level in your SQL by adding a new column to your dataset, or you can create a computed column and do the formatting there. There are a few options here.

jain

Hi Clement thanks for your reply, actually I am using scripted dataset, the period field datatype is String and I want to extract only the year from period, so in fetch method I have used the code "row["Year"] = row["Period"].substr(row["Period"].length - 4, 4)" its showing "Typeerror: cannot find the function substr". I have attached the rptdesign file for your reference, could you please let me know where went wrong?

Clement Wong

It's being seen as java.lang.String object instead of a Javascript primitive string type so you can use either: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>row["Year"] = row["Period"].substring((row["Period"].length()-4),row["Period"].length());</pre>
Or: 
<pre class='_prettyXprint _lang-auto _linenums:0'>row["Year"] = row["Period"].substring(13,17);</pre>
Or: 
<pre class='_prettyXprint _lang-auto _linenums:0'>row["Year"] = BirtStr.right(row["Period"],4);</pre>

jain

Thanks Clement, I have used the code which you have provided and its working as expected, thanks a lot.