topic Re: Strange date format when exporting to CSV in QlikView

Strange date format when exporting to CSV

Anonymous — Mon, 30 Nov 2015 19:44:36 GMT

Hello,

I am experiencing a strange issue.

I tried to load a QVD and export it to a CSV. The first column in my QVD is a date field. If I simply load all the columns

from the QVD and store into a CSV, the date field comes out as date (see below). But as soon as I specify a WHERE clause

in the load script, the date column becomes a date serial (see below). If I remove the WHERE clause and run it again, it stores

the date in the CSV. Date format is what I want and not date serial. I need to have a WHERE clause and have the date come out.

What do I need to do? This is kind of strange. I'm sure there is a reason. Thanks for your help.

Without the WHERE clause

7/1/2011 12:00:00 AM

7/7/2011 12:00:00 AM

With the WHERE clause

40730

40729

Re: Strange date format when exporting to CSV

sunny_talwar — Mon, 30 Nov 2015 19:54:13 GMT

May be wrap your date field around with a date function:

Date(DateField) as DateField

Re: Strange date format when exporting to CSV

satishkurra — Mon, 30 Nov 2015 19:56:19 GMT

Apart from Sunny T response,

Also try Date(Date#(DateField),'YYYY-MM-DD') as DateField to avoid further complications

Thanks

Satish

Re: Strange date format when exporting to CSV

Anonymous — Mon, 30 Nov 2015 20:01:56 GMT

Thanks. It worked. However it truncated the time portion of my date. How do I format this so I will not lose the time?

Re: Strange date format when exporting to CSV

Anonymous — Mon, 30 Nov 2015 20:02:47 GMT

Satish,

I get blanks for this column when I used your solution. Is there a formatting issue in your suggestion?

Re: Strange date format when exporting to CSV

roger_stone — Mon, 30 Nov 2015 20:34:59 GMT

Actually I think that when you run this without a WHERE clause, QlikView is interpreting the field as text rather than date/time. Can you share the WHERE clause with us?

Re: Strange date format when exporting to CSV

sunny_talwar — Mon, 30 Nov 2015 20:47:35 GMT

Try:

TimeStamp(DateField) as DateField

Re: Strange date format when exporting to CSV

Anonymous — Mon, 30 Nov 2015 20:49:21 GMT

The WHERE I included is not even on this date field. It's on another field which is text. I put in the WHERE so I

restrict the rows on the output.

Re: Strange date format when exporting to CSV

Anonymous — Mon, 30 Nov 2015 20:55:23 GMT

Sunny, this worked. Thanks!!

Re: Strange date format when exporting to CSV

roger_stone — Mon, 30 Nov 2015 21:00:18 GMT

OK, can you share the LOAD statement complete with WHERE clause so that we can get a better idea of what might be happening?

Re: Strange date format when exporting to CSV

sunny_talwar — Mon, 30 Nov 2015 21:03:03 GMT

Awesome

Re: Strange date format when exporting to CSV

roger_stone — Mon, 30 Nov 2015 21:07:43 GMT

Is that really putting out the exact format you showed above? i.e. 7/1/2011 12:00:00 AM

Re: Strange date format when exporting to CSV

sunny_talwar — Mon, 30 Nov 2015 21:10:17 GMT

If it isn't then you can always alter this SET statement:

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

Re: Strange date format when exporting to CSV

Anonymous — Tue, 01 Dec 2015 15:52:26 GMT

Roger,

Below are two screenshots. One showing without the WHERE clause and its output with the dates shown

correctly. The second screenshot shows the WHERE and its output where the dates are now date serial.

I've blanked "sensitive" info. Others have suggested to use Timestamp() or Date() which will work for me but

if you can figure out what's wrong with these screenshots, please do let me know. Thanks much.

Re: Strange date format when exporting to CSV

roger_stone — Wed, 02 Dec 2015 10:21:46 GMT

Sidney

I've tried to recreate this but my dates come out just fine. All I can think is that maybe col1 and col2 are maybe not formatted consistently within your QVD file but all records with 'RESTORATIVE CLNC' happen to have consistently-formatted dates.

Roger