Converting Invalid Date Data to Valid Date Format in PostgreSQL and SQL Server

In some instances, you may encounter date data that is not in the standard format. This can cause issues when trying to perform operations that require a valid date format. However, you can convert this invalid date data into a valid date format using custom expressions in Bold BI. Consider, we have string date like Jan’2023, Feb’2023…etc

PostgreSQL

In PostgreSQL, you can use the to_timestamp function to convert a string to a date format. The to_timestamp function requires two arguments: the string to be converted and the format of the string.

Here is an example of how you can use the to_timestamp function to convert a string in the format ‘Mon YY’ to a valid date format:

to_timestamp([Date], 'Mon YY')

Replace [Date] with the string field you want to convert.

SQL Server

In SQL Server, you can use the TRY_CAST function to convert a string to a date format. The TRY_CAST function attempts to convert an expression of one data type to another.

Here is an example of how you can use the TRY_CAST function to convert a string in the format ‘Mon YY’ to a valid date format:

TRY_CAST('20' + RIGHT([Date], 2) + LEFT([Date], 3) + '01' AS DATETIME)

Replace [Date] with the string field you want to convert.

References

• PostgreSQL Documentation: Date/Time Functions and Operators
• SQL Server Documentation: TRY_CAST (Transact-SQL)
• Expressions in Bold BI: Expressions in Bold BI