Is there a setting for date fields coming out of SQL Server that can be set differently for different users?

[Debra Waybright]

We have created a global variable in our space that is pulled from a SQL table and it is in a different format for me than for every other user in the company. I thought it is probably related to my administrator status, but others with admin rights don't have the issue.

This is how the global variable is created in the edasprof:

SET HOLDLIST = PRINTONLY

SET ASNAMES = ON

TABLE FILE ETL_PROCESS_LOG

SUM MAX.DATA_DATE AS 'DATA_AS_OF_DATE'

WHERE PROCESS_NAME EQ 'schedule_DAILY_LOAD'

WHERE PROCESS_EVENT_NAME EQ 'End'

ON TABLE HOLD AS HOLD_MAX_DATE

END

-RUN

-READFILE HOLD_MAX_DATE

-DEFAULTH &DATA_AS_OF_DATE='';

-SET &&DATA_DATE = EDIT(&DATA_AS_OF_DATE, '99/99/9999');

-RUN

This is what &&DATA_DATE looks like for everyone else:

06/28/2023

For me, I get:

20/23//06/

Ideas?

[Patrick Huebgen]

Looks like you are getting the date in a different order - is there any difference in your WebFOCUS user profile ?

in addition you might want to use a different way to transform your date from table to variable.

What is the format of your DATA_DATE field in the master file?

[Debra Waybright]

The date field is HYYMDs in the master file. I thought about the user profile, but I didn't see a profile file for my ID in the ibi/profiles folder. Maybe I'm looking in the wrong place?

[Patrick Huebgen]

Can you compare the output of this report if you run it vs. other users running the same report?

SET HOLDLIST = PRINTONLY

SET ASNAMES = ON

TABLE FILE ETL_PROCESS_LOG

SUM MAX.DATA_DATE AS 'DATA_AS_OF_DATE'

WHERE PROCESS_NAME EQ 'schedule_DAILY_LOAD'

WHERE PROCESS_EVENT_NAME EQ 'End'

ON TABLE PCHOLD AS HOLD_MAX_DATE

END

in addition

please run

WHENCE ETL_PROCESS_LOG MASTER

for both you and another user to see if there's any difference

[Debra Waybright]

I get the same results as others when I run those. Which is really odd, right? Because the date shows like this for me as well as others:I assume the WHENCE command is just saying, "where is this master file?" It returns the same file path.

[Patrick Huebgen]

Yes whence shows the location in case there are multiple / differfent copies of the master causing this problem

Please run

SET HOLDLIST = PRINTONLY

SET ASNAMES = ON

TABLE FILE ETL_PROCESS_LOG

SUM MAX.DATA_DATE AS 'DATA_AS_OF_DATE'

WHERE PROCESS_NAME EQ 'schedule_DAILY_LOAD'

WHERE PROCESS_EVENT_NAME EQ 'End'

ON TABLE AS HOLD_MAX_DATE

END

? HOLD HOLD_MAX_DATE

[Debra Waybright]

Again, it gives the same results.

WebFocus always finds new ways to confound me!

[Patrick Huebgen]

What was the answer - what is the format of output field

[Debra Waybright]

 focus_format="MDYY"

[Martin Yergeau]

And do you have same regional settings than others on your computer ?

Looks like others are USA (which is MM/DD/YYYY) and you are Canadian/French (which is YYYY/MM/DD)

[Debra Waybright]

That's a good thought, but not it. My regional settings on my computer are "English (United States)" and the Short date format is mm/dd/yyyy.

I think WebFocus just likes messing with me 😅