I am trying to change the format of a timestamp field to a date field in a certain format with AWS Redshift.
I googled a lot and the common "best practice" that I found was to cast the timestamp to a date and then use to_char to bring it into the right format. In the end I want to use the date field in a Qlik Sense dashboard where it is input for a date picker extension that apparently requires the format DD/MM/YYYY to work.
Current date format in the DB:
9/2/2019 6:38:00 AM (which I would describe as M/D/YYYY H:MM:SS ZZ)
Desired output:
DD/MM/YYYY, resulting in the value 02/09/2019
Current status:
to_char(cast(timestamp_field as date), 'DD/MM/YYYY') --> result: 02/09/2019
However, the date picker extension in Qlik Sense still does not work and I guess that's because the output is a string and not a date. Casting the string to a date returns in an error.
cast(to_char(cast(timestamp_field as date), 'DD/MM/YYYY') as date) as date_picker_date
Connector reply error: SQL##f - SqlState: 57014, ErrorCode: 30, ErrorMsg: [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 57014] ERROR: Error converting text to date
I am new to Redshift and would have expected to be able to cast to date with a format string as parameter, but apparently that's not a thing. Can someone enlighten me on how to solve this?
You can try with the timestamp at the beginning of the script of the QlikSense. It will be at the start of the script. in the Main section as shown below:
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
here SET is being used to define the time stamp in that application. You can try changing here to your requirements. Else 2 options 1 use a stamp which you have already used or create your own extension from the lib Stamp use can be like this:
Datestamp_Table:
Load *,
Date(Date#(StringDate,'M/D/YY'),'DD/MMM/YY') as Date;
LOAD * INLINE [
StringDate
8/7/97
8/6/97];
Output will be like this:
Stringdate Date
8/7/97 07/Aug/97
8/6/97 06/Aug/97