Removing Time Stamp From Date

[#1]

Hi

I am currently trying to create a calendar based on data I am loading from a database table using SQL Select.  The initial date field contains a time stamp which I want to strip out.  The reason for this is that I have found that when I export the chart information created in QlikView from the data into Excel it is displaying the date incorrectly. A 'Week End' date displayed as 07/09/2014 in QlikView is shown as 08/09/2014 in Excel. The contents of the cell are 07/09/2014  00:00:00 but it is displayed as 08/09/2014.

I have tried removing the time stamp from the data loaded into QlikView with a preceding load statement but it is still exporting the date to Excel with a time stamp.

Table:

LOAD [Customer]

          ,[Order No]

          ,Date ([Dispatch Date],'DD/MM/YYYY') As [Dispatch Date]

          ,etc;

SQL Select *

From Datawarehouse.dbo.Orders

Where .......;

TempDates:

LOAD NUM(Min([Dispatch Date],)) as MinDate,

  NUM(MAX([Dispatch Date],)) as MaxDate

RESIDENT Table;

LET vMinDate = peek('MinDate',0,'TempDates');

LET vMaxDate = peek('MaxDate',0,'TempDates');

Drop Table TempDates;

TempCalendar:

LOAD

  $(vMinDate) + ROWNO() - 1 AS Num,

  DATE($(vMinDate) + ROWNO() - 1) AS TempDate

  AUTOGENERATE $(vMaxDate) - $(vMinDate) + 1;

OrderCalendar:

LOAD

          Usual DATE definitions,

          WeekEnd(TempDate) As WeekEnd,

          etc;

Initially I tried a straight forward CONVERT statement (date format 103) within the SQL Select (having defined the fields to bring in and with no preceding load) but when exporting the data from the QlikView chart it still had a time stamp.

How can I bring in only the date part of the source data or manipulate it with in QlikView so that the TempDate & WeekEnd are exported without a time stamp.

Your help is much appreciated.

[rubenmarin · Accepted solution]

Hi Gareth, Weekend returns 07/09/2014 23:59:59.999, maybe Excel is rounding

In OrderCalendar try:

Date(Floor(WeekEnd(TempDate)),'DD/MM/YYYY')) As WeekEnd,

If I create a table with the expression

WeekEnd(Date('07/09/2014 00:00:00', 'DD/MM/YYYY')) It exports as 08/09/2014

If I create a table with the expression

Date(Floor(WeekEnd(Date('07/09/2014 00:00:00', 'DD/MM/YYYY'))), 'DD/MM/YYYY') it exports as 07/09/2014

[stigchel]

In the preceding load you can use:

Date(Floor([Dispatch Date]),'DD/MM/YYYY') as [Dispatch Date]

[maxgro]

if you have a datetime  (now() in the example), use floor and frac to get the date or the time 

[Gabriel]

Hi Gareth,

Try

=Date(Floor(DateField()))          AS [Dispatch Date]

Let us know if that work

[#6]

Hi Gabriel

You asked to let you know if it works. It does but only when applied to the calendar statement DATE(Floor(WeekEnd(TempDate)),'DD/MM/YYYY')AS WeekEnd .

When applied to the preload it did not. I will add this to the Community to share the result

Many thanks,

Gareth

[#7]

Thanks for all the replies which all used same approach.  However, for information please note that it only worked when applied to the calendar statement, ie

Date(Floor(WeekEnd(TempDate))''DD/MM/YYYY') As WeekEnd

When applied to the preload and it did not make a difference to the error I was experiencing.

Thanks to all