Converting string to Date 'DDMMYY' format

[#1]

Hi,

I have loaded a series of dates below.

LOAD

    Sales Agent,

    "42158",

    "42165",

    "42172",

    "42179",

    "42186",

    "42193",

    "42200",

    "42207",

    "42214"

How can I convert them into DDMMYY format? I've seen a few suggestions online but wasn't able to get it working... thanks for your help!

[jonathandienst]

Are these column names? Are you cross-table loading them?

If they are column names and you not cross table loading them, and if you know the min and max dates, you could create a mapping table, and rename the fields using the mapping table. For example:

MapNames:

Mapping LOAD

     RecNo() + 42157 As DateNum,

     Date(RecNo() + 42157) As Date

Autogenerate (42214 - 42158 + 1);

Rename Fields using MapNames;

[#3]

Hi Jonathan, sorry I should have mentioned that the dates are crosstabled.

Crosstable(Date,Sales)

LOAD

    Sales Agent,

    "42158",

    "42165",

    "42172",

    "42179",

    "42186",

    "42193",

    "42200",

    "42207",

    "42214"

FROM [lib://Allen/time-series....xlsx]

[#4]

juz use Date(fieldnme,'DDMMYYYY')

[#5]

That doesn't work... Sales Agent is not a date field...

[jonathandienst]

Like this then:

T_Data:

Crosstable(Date,Sales)

LOAD

    Sales Agent,

    "42158",

    "42165",

    "42172",

    "42179",

    "42186",

    "42193",

    "42200",

    "42207",

    "42214"

FROM [lib://Allen/time-series....xlsx]

Data:

NoConcatenate

LOAD [Sales Agent],

  Date(Date) As Date,

  Sales

Resident T_Data;

DROP TABLE T_Data;

[jonathandienst]

If Date(Date) is not giving the format you desire, then use something like Date(Date, 'DDMMYY') to get the format you require.

[#8]

Hmm.. I've tried your suggestion, but I still get dates in format like 42158, for example. Any suggestion?

[jyothish8807]

HI Allen,

Try like this:

date(trim(replace([Sales Agent],'"','')),'MMDDYY') as New field

Regards

KC

[ganeshreddy]

Hi Jyotish and Allen,

Allen saying that Sales Agent is not the Date field, i advice him to replace it by Date field on your code. I hope it helps him.

Cheers,

Ganesh.