Convert to Date Format

[eduardo_dimperio]

Hi everyone,

I have this situation, a crosstable from a excel file with a Date Field (in number format) and i tried everything that i know to convert that number to a date format.

To Data = 43017

INPUT:

date(DATA)

date#( DATA, 'M/D/YY')

NUM(DATA)

TEXT(DATA)

TIMESTAMP(DATA,'DD/MM/YYYY')

OUTPUT:

null

43017

null

43017

null

[marcelviegas]

insert the example

[eduardo_dimperio]

Just to compliment, i'll post my code bellow:

TMP:

Crosstable (Data, NAME_SYSTEM)

LOAD

*

FROM [lib://VisitaMI (bi_bi.desenvolvimento)/CONTROLE - AGENDAMENTO -2016-2017-2018 V*.xlsx]

(ooxml, embedded labels, table is Geral);

Noconcatenate

AUX:

Load

    Unidades AS UNIDADES,

    OID_SYSTEM,

    Data AS DATA,

    Funcionários AS FUNCIONARIO,

'Agendado' AS AGENDAMENTO

resident TMP

WHERE Data>NUM(TODAY()-5);

drop table TMP;

[swuehl · Accepted solution]

Maybe

Date(Num#(DATA))

[eduardo_dimperio]

Hi marcel,

code bellow

[eduardo_dimperio]

Yeah!

But why this work and others not?

[swuehl]

The CROSSTABLE LOAD prefix will return the column headers as field values in your new DATA column, but unfortunately as pure text values.

Pure text values can't be formatted using formatting functions Date() or Timestamp(), or Num().

Pure text values can be interpreted using interpretation functions, but Date#(DATA, 'M/D/YY') is not using the correct format code for your integer number. Instead, use Num#() to interpret the text as number.

A correctly interpreted number can then be formatted as Date using Date() or Timestamp().

[eduardo_dimperio]

Stefan thanks for the explanation