Reformatting date fields 1 at a time… Must be a better way!
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;
Re: Reformatting date fields 1 at a time… Must be a better way!
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 ;