Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Need a little help. I have converted the format of my service date from MM/DD/YYYY to YYYY-MM-DD in my script using the following: Date(Service_Date,'YYYY-MM-DD') as Serv_Date . Then use the following expression: Date(Max(Serv_Date)) it returns MM/DD/YYYY format even though I am using the one I converted in the script by using the following : YYY-YMM-DD. Any ideas?
Never mind. I went to the numeric tab and changed the format there, but it makes no sense why it would not take the format I gave it in the script.
When you use the max function it converts the date into Numeric format over which you have applied date function. So Date function will convert it into the default format which is define in the script.
So change the set variable of Date
SET DateFormat='YYY-YMM-DD';
there is a default format at the start of every script
SET DateFormat='.............';
the date function
date(somefield) will use that format
if you want to change the default format, change the
SET DateFormat ='YYYY-MM-DD';
or
if you only want to change the format in some part of the script use the second parameter in date function
date(somefield, 'YYYY-MM-DD')
Hi Thom,
You have set the Default date format in script to
SET DateFormat='YYY-MMM-DD';
then only it works. By default if you use any date functions it returns in the above set date format. If you want other than this you have to specify the Date format using Date().
Regards,
Jagan.
Hi,
SET DateFormat ='YYYY-MM-DD';
Hi
Please use below script:
SET DateFormat='M/DD/YYYY';
SET TimestampFormat='M/DD/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
Table:
LOAD date(Serv_Date) as Serv_Date Inline [
Serv_Date
6/25/2010
1/29/2011
12/21/2013
12/21/2014
06/21/2015
06/21/2013
]
;
left Join (Table)
LOAD
max(date(Serv_Date)) as Max_Date
Resident Table;
EXIT Script;