NUMERIC VALUE CONVERT TO DATE ISSUE

[#1]

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.

[Anonymous]

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.

[maxgro]

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

Get the Dates Right

[arusanah]

=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 !

[#5]

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

[MarcoWedel]

DayName(yourfield)

will also return the date part of your timestamp value.

regards

Marco

[MarcoWedel]

please close your thread if your question is answered.

thanks

regards

Marco

[krishna20]

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.