upper(date(DATE,'DD-MMM-YYYY')) STORE AS NUMERIC FOR SORTING?

[cmorri1988]

Hi there, Wondering if I can store upper(date(DATE,'DD-MMM-YYYY')) as numeric for sorting purposes by using other functions within this statement? I already order the table by another field, and cant change the system MonthName variable to uppercase values, as I don't want all date formats to change to uppercase throughout the script. Any help appreciated, Thanks

[YoussefBelloum]

Hi,

how to you want to UPPER numeric values ?

[cmorri1988]

Yes, I believe I need:

date#(upper(date(DATE,'DD-MMM-YYYY')),'DD-MMM-YYYY')

Thanks

[YoussefBelloum]

I didn't see the MMM part...

Date() is dual data type function, it return also a numeric value behind the value it displays, did you try to sort using only the Date() function ?

what do you have so far ?

[marcus_sommer]

I think you should adjust the standard-variables for this task at the beginning of your script instead of manipulating the date-fields with any string-functions: Format Variable Tweaks

- Marcus

[sunny_talwar]

Try like this in environmental variable

SET MONTHNAMES='JAN;FEB;MAR;APR;MAY;JUN;JUL;AUG;SEP;OCT;NOV;DEC';

and then this

Date(DATE,'DD-MMM-YYYY') as Date