Convert VarChar to Date
7 April, 2015
Is it possible to convert a varchar field to a date field? I currently have a varchar field with a date and it will not correctly display the format option of "Other" with mm/dd.
I need to display the field as mm/dd and am unable to do that.
Thank you.
I need to display the field as mm/dd and am unable to do that.
Thank you.
Hi,
There are several ways to convert a VARCHAR to Date within Yellowfin itself. This can be accomplished by utilizing SQL built in functions. There are several ways to accomplish this:
1. Build free hand SQL Calculated fields (at either the view or report level)
2. Build a free hand SQL Report
3. Build a free hand SQL view
4. Utilize Virtual tables
Depending on the database you are using the SQL required to accomplish this can vary.
I personally believe that using option 1 is the easiest, and since I use PostgreSQL, I created a calculated field using the following syntax:
to_date(, <'dateFormat'>)
As you can see, my column on the left, Start Date, is a VARCHAR, and my new column Test on the right, is a Date field and can leverage Yellowfin date functions.
Remember these functions are database specific!
Here are a couple of links to some popular databases that discusses how to accomplish this:
SQL Server: SQL Server Functions that helps to convert date and time values to and from string literals and other date and time formats.
MySQL: DATE_FORMAT
PostgreSQL: Data Type Formatting Functions
There are several ways to convert a VARCHAR to Date within Yellowfin itself. This can be accomplished by utilizing SQL built in functions. There are several ways to accomplish this:
1. Build free hand SQL Calculated fields (at either the view or report level)
2. Build a free hand SQL Report
3. Build a free hand SQL view
4. Utilize Virtual tables
Depending on the database you are using the SQL required to accomplish this can vary.
I personally believe that using option 1 is the easiest, and since I use PostgreSQL, I created a calculated field using the following syntax:
to_date(
As you can see, my column on the left, Start Date, is a VARCHAR, and my new column Test on the right, is a Date field and can leverage Yellowfin date functions.
Remember these functions are database specific!
Here are a couple of links to some popular databases that discusses how to accomplish this:
SQL Server: SQL Server Functions that helps to convert date and time values to and from string literals and other date and time formats.
MySQL: DATE_FORMAT
PostgreSQL: Data Type Formatting Functions
Thank You!
Hi there,
I updated my previous post to include some new methods that I wasn't aware of when I originally responded.
Sorry for any inconvenience and I hope that some of this information is helpful to you.
Kind Regards,
Dustin
Best regards,
The Yellowfin Support Team
Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
I updated my previous post to include some new methods that I wasn't aware of when I originally responded.
Sorry for any inconvenience and I hope that some of this information is helpful to you.
Kind Regards,
Dustin
Best regards,
The Yellowfin Support Team
Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4