Converting m/d/yyyy" "h\:mm\:ss AM/PM to Monthstart 'MMM-YY'

[#1]

Hi,

I've looked up several different threads to convert a date field, however I'm still unable to convert this particular custom date into the appropriate date format; I've tried subfield too, but doesn't seem to work.

m/d/yyyy"  "h\:mm\:ss AM/PM and appears like this in excel 8/1/2016  2:15:31 PM

I'd like for this to show up as in the format of:

date(monthstart([Invoice Date]),'MMM-YY') as [FC Date]

[#2]

Hi,

How about:

Date(MonthStart(Date#([Invoice Date],'M/D/YYYY  hh:mm:ss tt')),'MMM-YY')

Tested with this:

LOAD
Date(MonthStart(Date#(F1,'M/D/YYYY  hh:mm:ss tt')),'MMM-YY') as F1
INLINE [
    F1
    8/1/2016  2:15:31 PM
];

and it worked.

BR,

Kuba

[maxgro]

using subfield

subfield([Invoice Date], ' ', 1)

should returns the date part of the string (8/1/2016), then you can use date# and monthstart

[#4]

Hi,

I tried using date(monthstart((subfield([Invoice Date], ' ',1),'MMM-YY') as [FC Date]

But I'd get an error.

[#5]

[#6]

I think the problem is due to the date format in excel being a custom formatting.

[#7]

I've figured out what's wrong....

I'm running the subfield(date) on a group.

How can I go about fixing this?

[maxgro]

maybe you can post your excel...., just some lines

and the script

[santiago_respane]

Hi,

I would go with the subfield option with something like this:

Date(Monthstart(DATE#(subfield([Invoice Date], '  ', 1),'m/d/yyyy')),'MMM-YY') as DATE

As you are using excel custom date format, it is hard to determine what the middle character is (tab, space, two spaces)

m/d/yyyy"  "h\:mm\:ss AM/PM -> in your case there are two spaces

maybe it is better if you share your excel in order to give you a better answer.

Kind regards,

[#10 · Accepted solution]

you must group by REFERENCE as well as subfield(DATASHIPPED,' ',1)

GROUP BY REFERENCE, subfield(DATASHIPPED,' ',1)

PS. Considering your date format is really very custom, going with subfield is very good suggestion. For sure go with this solution as gentlemen below suggested.