

Partner - Contributor III
2019-05-09
02:13 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Change Date format from Text to Date
Hi Experts,
I have to change the format of year column from text to date format. I have the Year data in following format.
Fixed -Pay % | Varaible -Pay % | Pers.No. | Year |
0 | 0 | 10000398 | 2019-20 |
0 | 0 | 10000442 | 2019-20 |
0 | 0 | 10000447 | 2019-20 |
0 | 0 | 10000454 | 2019-20 |
0 | 0 | 10000477 | 2019-20 |
0.001110487 | 0.196100604 | 10000001 | 2019-20 |
0.00647306 | 0.230132449 | 10000022 | 2019-20 |
0.007262928 | 0.188228022 | 10000057 | 2019-20 |
0.008715513 | 0.281253869 | 10000133 | 2019-20 |
0.010282058 | 0.327032225 | 10000039 | 2019-20 |
0.01116693 | 0.10854293 | 10000446 | 2019-20 |
I need the output year column as date format and in year column as date format 2019-2020.
Please suggest .
- Tags:
- date
976 Views
4 Replies

Luminary
2019-05-09
02:31 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello! How about this:
DATE(DATE#(LEFT(Year,4),'YYYY'),'YYYY') & '-' & DATE(DATE#(RIGHT(Year,2),'YY'),'YYYY') AS Year
vizmind.eu
960 Views


Partner - Contributor III
2019-05-09
02:42 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
& '-' & using this the format is still in text. It's not in number format.
957 Views

Luminary
2019-05-09
02:47 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can of course separate that
DATE(DATE#(LEFT(Year,4),'YYYY'),'YYYY') AS Year1,
DATE(DATE#(RIGHT(Year,2),'YY'),'YYYY') AS Year2
and then use the following as dimension in your chart
Year1 & '-' & Year2
vizmind.eu
952 Views

MVP
2019-05-09
03:09 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Your one year field value is '2019-20' and you want this value to be in date format ? Could you explain a bit more. Usually in such financial year representation it is recommended to use dual(), may be something like:
Dual(YearField, Left(YearField,4)) as Year
This will give you value (text at at the UI) '2019-20' and a numeric value 2019 at the underlying value you can perform mathematical operation on.
946 Views
