topic Seeking Best Practices for Loading Dates into QlikView from SQL Server in QlikView

Seeking Best Practices for Loading Dates into QlikView from SQL Server

ellyodelly — Fri, 08 Aug 2014 17:49:07 GMT

Most would agree to standardized how most dates are loaded.

Do you standardized or format so they are all loaded the same?

do you do it on the SQL side ie. convert(date,SomeDateField,101) then create a .qvd to load

or do you do it on QV load into the dashboard date(SomeDateField,"MM/DD/YYYY")?

if it is a date field and in the script file you set the

set DateFormat='MM/DD/YYYY'; do you still take the time to format the date individually?

just some thoughts- wondering how others address this.

Re: Seeking Best Practices for Loading Dates into QlikView from SQL Server

whiteline — Sat, 09 Aug 2014 06:22:00 GMT

Hi.

Usually the thing to care about is to load the dates as numbers instead of strings.

The text format you can change later. Sometimes you have to do it to use different for some charts.

Setting the right DateFormat corresponding the source allows QV to handle the conversion automatically. Otherwise you will have to use # functions.

Re: Seeking Best Practices for Loading Dates into QlikView from SQL Server

ellyodelly — Wed, 01 Oct 2014 12:34:55 GMT

Good Morning- and I offer my apologies- I did not see this hiding in my messages. I thank you for the response. So I just wanted to clarify. when you are selecting out of SQL Server a date INVOICE_DATE you would force the conversion ie... select convert(bigint,INVOICE_DATE) as invoice_date_as_number from...?

then when using that number in QLIK you will format it into a date? #DATE or DATE

Re: Seeking Best Practices for Loading Dates into QlikView from SQL Server

whiteline — Sat, 04 Oct 2014 18:48:41 GMT

Hi.

Try to select without convert() and check if QV stores the numbers or strings.

#Date functions read the string and create dual (both number and text, the way QV handles the dates) according to the format string provided.

Date functions take the dual and change the text only according to the format string provided.