Conversion of date formats in data in Bold BI

Published:

In Bold BI, date conversion involves transforming data between different date formats, such as converting strings to dates or dates to strings. Here are some potential functions that can help accomplish this.

1) String to Date:

Please utilize these articles for reference on how to convert string type data to date type in Bold BI.

2) Date to String:

SQL Server:

In SQL, you can convert a date to a string using the CAST or CONVERT functions, depending on the database system you are using. Here are examples using both:

Sample Data:

Example 1: CAST()
The syntax CAST(your_date_column AS data_type)

CAST: CAST is a SQL function used to convert one data type into another.
your_date_column: This is the column or expression containing the date you want to convert.
AS VARCHAR: This specifies the target data type to which you want to cast the value. In this case, it converts the date to a variable-length character string (VARCHAR).
```
   CAST([OrderDate] AS VARCHAR)
```

Example 2: CONVERT()
The syntax CONVERT(data_type, your_date_column, 120) is specific to SQL Server for converting a date to a string.

CONVERT(VARCHAR, [OrderDate], 120)

Explanation:

CONVERT: This is a SQL Server function used for converting a value from one data type to another.
VARCHAR: This specifies the target data type to which you want to convert the value. In this case, it converts the date to a variable-length character string.
your_date_column: This is the column or expression containing the date you want to convert.
120: This is the style code specifying the format for the conversion. In SQL Server, style code 120 represents the ODBC canonical date and time format (yyyy-mm-dd hh:mi:ss).

So, CONVERT(VARCHAR, your_date_column, 120) converts the date in your_date_column to a string in the format ‘yyyy-mm-dd hh:mi:ss’.

Example 3: FORMAT()
Please specify the desired format for displaying dates as ‘MM_dd_yyyy’, ‘MMM-dd-yy’, ‘yyyy-dd-MM’, ‘yyyy-dd-MM hh.mm’, ‘yyyy-dd-MM hh.mm.ss’, and ‘yyyy-dd-MM hh.mm.ss tt’.

FORMAT([OrderDate], 'hh:mm:ss')

MySQL / PostgreSQL:

The syntax DATE_FORMAT(your_date_column, ‘%Y-%m-%d’) can be used in MySQL and Postgres for date conversion
Example 4: DATE_FORMAT()

DATE_FORMAT([HireDate], '%Y-%m-%d')

Explanation:

DATE_FORMAT: This is a MySQL function used to format dates as strings.
your_date_column: This is the column or expression containing the date you want to format.
- ‘%Y-%m-%d’: This is the format specifier string that determines how the date will be formatted:
- %Y: Represents the year with century as a numeric, four-digit value (e.g., 2022).
- %m: Represents the month as a numeric, two-digit value (01 for January, 02 for February, and so on).
- %d: Represents the day of the month as a numeric, two-digit value (01 through 31).

When applied to your_date_column, DATE_FORMAT(your_date_column, ‘%Y-%m-%d’) converts the date into a string formatted as ‘YYYY-MM-DD’. For example, ‘2024-04-05’ represents April 5, 2024.

Example 5: TO_CHAR()
If you need to specify the particular format for the date, then use the following one:

TO_CHAR(NOW(), ‘DD-Mon-YYYY HH24:MI:SS’)

Additional References

Was this article useful?

Dislike

Written by Gayathri Ravichandran

Updated

Comments (0)