Date format in script

[rdsuperlike]

I am trying to display date. I can see it as date format in the table box but when I do the selection it shows the number.

What should I do to the due_dt to get the date values here. I tried

Floor(date(DMND_DUE_DT)) in the script. But vouldnt get the results.

Any inputs?

[maxgro]

try without floor

[chrismarlow]

Is your table showing a date because you have formatted it specifically? You can set at a document level by using Settings - Document Properties - Number tab.

[#4]

I noticed you don't have a format specified in your "date" command.

I have a similar situation; clients login at all times, and 'dlogin' captures the date and time they logged in. One corporate user wanted to be able to select a 'day'. Most days, we have 1000's of client logins, so just giving a list box of "dlogin" wouldn't work. I used:

date(floor(dlogin),'MM-DD-YY') <- that's our standard date representation

Came out perfect in the list box, but when you select a date, you see "4981 of 3756453'.

In your case, I would suggest adding the date format, in whatever format you want, to the expression.

[MarcoWedel · Accepted solution]

Hi,

maybe you just formatted the DMND_DUE_DT (or the result of your Floor(date(DMND_DUE_DT)) expression) as date in some front end objects instead of loading the field as date:

Try to format the field using the Date function in the script.

LOAD Date(DMND_DUE_DT) as DMND_DUE_DT,
    DMND_DUE_DT as DMND_DUE_DT2,
    DMND_DUE_DT as DMND_DUE_DT3
Inline [
DMND_DUE_DT
42196
];

Regarding your script expression:

Floor(date(DMND_DUE_DT))

it calculates the integer part of the numerical value of the DMND_DUE_DT field and returns a number, not a date. The date() function within the Floor() function in the other hand just formats the already exisiting numerical representation of this field and has no effect, because Floor() will still return a number.

So just try the other way around:

Date(Floor(DMND_DUE_DT))

(only makes sense if DMND_DUE_DT is a timestamp instead of a date, i.e. it has a fractional (=time) part)

hope this helps

regards

Marco

[MarcoWedel]

Hi,

"4981 of 3756453' in your current selections box means 4981 field values of 3756453 existing values are selected.

Showing all those 4981 values otherwise would require a very large box ...

hope this helps

regards

Marco

[#7]

Please excuse me for giving the impression I didn't understand what "4819 of 365675" meant. I know perfectly well what it means, and I was highlighting that for the original poster's edification, since at the time it appeared he had multiple items on each day. Now that he has explained in more detail what his data looks like, this wouldn't be an issue.

[jagan]

Hi,

You should not use Floor() as outer function instead you can use like below to round off to date.  Floor() will round off to the lower integer and converts it to the number.

LOAD

*,

Date(Floor(Date)) AS Date_Formatted

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

[engishfaque]

Dear Rdsuperlike,

Try these,

date(Num#(YourDateFieldName),'DD/MM/YYYY') as NewDateField

or

date(Num(YourDateFieldName),'DD/MM/YYYY') as NewDateField

Kind regards,

Ishfaque Ahmed