Dates pulled as text from Table View

[#1]

Hello, Community-

The database access I have is to Views, not to the Oracle database itself. (Don't know if this is relevant or not).

I'm trying to create a report off of a view that has the field "Period" - it's an SQL varchar string field, not a date.  The string only captures Month and Year .... Jan-15, Feb-15, etc.

I've tried Dual, Date#, and other posted community solutions, both in the script and the sorting, and no matter what I do, my dates still sort as alpha (Apr-02, Aug-01, Dec-01, Feb-02, Jan-02...)

Can anyone help?  Does being required to pull from views make a difference in general?

Thanks

[sunny_talwar]

You can extract the 3 letter month name using the left function and then use mapping load to convert Jan to 1, Feb to 2 and so on and then you can create a date with the month names using MakeDate Function.

This might be a long way to do a easy thing, but don't know any better way to do it.

Best,

S

[#3]

Hi,

Can you try following expression DATE#(Period,'MMM-YY')

Regards

Nandkishor S. Pandirkar

[Anonymous · Accepted solution]

Date# is my preference.  Maybe you did something wrong?  Try

date(date#(Period, 'MMM-YY'), 'MMM-YY')

It has nothing to do with it being an Oracle view.

[maxgro]

=date(Date#('Jan-15', 'MMM-YY'))

=date(Date#(YourOracleField, 'MMM-YY'))

[sunny_talwar]

msolomov, this is totally unrelated, but would you be able to tell me what is a difference between DATE function and Date# function. I have seen a lot of posts which discuss the difference and explain them in details, but never really understood the difference.

Thanks in Advance,

Best,

S

[Anonymous]

S,

The date# tells how to read the data, that is date#(Period, 'MMM-YY') says "Read the first three characters as month, and the two after dash as year.

The date() function says how to represent the data, so date(today(), 'WWWW, MMMM DD YYYY') returns Friday, February 13 2015 - no matter what is the default format of the date is used.

Regards,

Michael

[#8]

This didn't do it, unfortunately.

[sunny_talwar]

That totally makes sense. Thanks for your time on explaining the difference between the two.

Best,

S

[#10]

BINGO!!!!!!!!! I think I left out something when I did date# before, and then fixed the sort in the listbox expression. thanks!