Reformatting date fields 1 at a time… Must be a better way!

[wdchristensen]

I have several apps that pull data from SQL Server and the dates are returned in the default database format of YYYY-M-D. I can use the “SET DateFormat='YYYY-M-D';” at the beginning of the load so the fields are recognized as dates but when I add the field as a filter in the application it displays in default SQL format but I want the date to show as M/D/YYYY. So I should be able to set the date format at the end of the script to “SET DateFormat='M/D/YYYY';” and change the default display for the string portion of the date, right? Unfortunately this won’t work. So I load every dataset to a resident table reformat the dates one field at a time. This is time consuming and makes for long scripts that do next to nothing. There has to be a better way. Please teach me a better way!  

SET DateFormat='YYYY-M-D';
Test_01:
LOAD
date(today()-1, 'YYYY-M-D') as _Yesterday
,date(today(), 'YYYY-M-D') as _Today
,date(today()+1, 'YYYY-M-D') as _Tomorrow,
RecNo() as Num
autogenerate 1
;
SET DateFormat='M/D/YYYY';
Test_02:
Noconcatenate
Load
_Today as unformatted_Today
,Date(Date#(_Yesterday,'YYYY-M-D'), 'M/D/YYYY') as _Yesterday_01
,Date(Date#(_Today,'YYYY-M-D'), 'M/D/YYYY') as _Today_01
,Date(Date#(_Tomorrow,'YYYY-M-D'), 'M/D/YYYY') as _Tomorrow_01
Resident Test_01;

Exit Script;

[wdchristensen · Accepted solution]

Thanks rwunderlich for answering my question! Your help is much appreciated.

https://qlikviewcookbook.com/2016/07/touchless-formatting/

[wdchristensen]

Example solution:

SET DateFormat='YYYY-M-D';

FormatTemplate_01:
LOAD
date(0, 'M/D/YYYY') as _Yesterday
,date(0, 'M/D/YYYY') as _Today
,date(0, 'M/D/YYYY') as _Tomorrow
autogenerate 0
;

Test_01:
LOAD
today() as DefaultFormatForToday
,Num(today()) as NumFormatForToday
,today()-1 as _Yesterday
,Num(today()) as _Today
,date(today()+1, 'YYYY-M-D') as _Tomorrow
,RecNo() as Num
autogenerate 1
;

DROP Table FormatTemplate_01;

Exit Script;