topic Re: NUMERIC VALUE CONVERT TO DATE ISSUE in QlikView

NUMERIC VALUE CONVERT TO DATE ISSUE

Thu, 21 May 2015 19:40:47 GMT

Good afternoon all,

I am new to QV, so please excuse the potential simplicity of my question. I have just pulled in a table file from a Microsoft SQL server. I am looking to get the date range however, when I make a list box and what's supposed to show as the dates is showing as numeric values with decimal points. For an example, please see attached, the left hand side is the dates and the right hand side is the unit. When I pull the data into excel I am getting the date mm/dd/yy plus the time of date (hh:mm) thus it looks like this ==> mm/dd/yyyy hh:mm.

How do I rectify this issue so that the numeric values are displayed as dates i.e. Jan, Feb, etc?

Appreciate any and all help.

Re: NUMERIC VALUE CONVERT TO DATE ISSUE

Anonymous — Thu, 21 May 2015 21:57:34 GMT

You can do it in the script using timestamp() function:

timestamp(YourFieldName) as YourFieldName

It will use your system format. If you want to specify any other format:

timestamp(YourFieldName, 'MM/DD/YYYY hh:mm') as YourFieldName

If you want only date without time:

date(floor(YourFieldName)) as YourFieldName

If you cannot change the script, go to the Document properties, tab Number, and change format settings for this field to timestamp or date.

Re: NUMERIC VALUE CONVERT TO DATE ISSUE

maxgro — Thu, 21 May 2015 23:08:11 GMT

if you want more detail, there are some post of HIC

Get the Dates Right

Re: NUMERIC VALUE CONVERT TO DATE ISSUE

arusanah — Fri, 22 May 2015 04:40:19 GMT

=month(date(today(),'MMM'))

if want to create a list box with values as Jan, feb,........ Dec.

then click rightclick ->new sheet object->-> listbox-> in general lab under field select expression -> type above expression . replaye today() , with your <date field >

hope it helps !

Re: NUMERIC VALUE CONVERT TO DATE ISSUE

Wed, 03 Jun 2015 18:51:20 GMT

All, Thank you very much! Above worked very well!

Re: NUMERIC VALUE CONVERT TO DATE ISSUE

MarcoWedel — Mon, 29 Jun 2015 19:26:47 GMT

DayName(yourfield)

will also return the date part of your timestamp value.

regards

Marco

Re: NUMERIC VALUE CONVERT TO DATE ISSUE

MarcoWedel — Mon, 29 Jun 2015 19:28:16 GMT

please close your thread if your question is answered.

thanks

regards

Marco

Re: NUMERIC VALUE CONVERT TO DATE ISSUE

krishna20 — Tue, 30 Jun 2015 06:19:40 GMT

Hi,

Your date field is with time stamp. If you wanna see dates without changing in the script. Go to listbox properties -> Number -> Override settings -> Select Time stamp.