Date and time fields- How to create derived fields

[#1]

Hello Everyone! I need your help for configuring date and time fields.

I have the date in the following format: 201503111647000 ( 2015 03 11 16:47:000). The field is called "Fecha" in the database and it is in text format.

How can I create the derived fields from the field "Fecha"?

I need the Year (2015), Month (11) ,Day (3), Hour (16:47), Month names( Jan, Feb,..), day names (Monday, Tuesday..), year month names( Jan 15, Feb 15)

I would really appreciate your help .Thank you!!!

LOAD

    Cliente,

    Sucursal,

    Vendedor,

    "Unidad de Medida",

    "Condcion_Comercial",

    Cantidad,

    "Costo Total",

    "Ventas Netas",

     Fecha,

SQL SELECT

    Cliente,

    Sucursal,

    Vendedor,

    "Unidad de Medida",

    "Condcion_Comercial" as "Condición Comercial",

    Cantidad,

    "Costo Total",

    "Ventas Netas",

    "Contenido total Vendido",

    Transaccion,

    "Tipo de comprobante",

    Fecha

FROM "C:\USERS\IGNACIO\Documents\Proyecto Pintecord\Base de datos Ventas.accdb".Ventas;

[sunny_talwar]

I don't think its a time zone issue. I mistakenly gave you the wrong code:

TimeStamp(TimeStamp#(Fecha, 'YYYYMMDDhhmmsss'), 'YYYY MM DD hh:mm:ss') //wrong

vs.

TimeStamp(TimeStamp#(Fecha, 'YYYYMMDDhhmmssfff'), 'YYYY MM DD hh:mm:ss TT')

Notice the extra fff at the end of the correct code

[#3]

Perfect! The complete scrip is the following:

Thank you very much

I keep wondering if it is better to use a declare definition and derive fields, if anyone knows how I would appreciatte.

LOAD *,

  Month(Fecha)&'-'& Year(Fecha) as MonthYear;

LOAD *,

  Year(Fecha) as Year,

    Month(Fecha) as Month,

    Time(Frac(Fecha)) as Time,

    Date(Floor(Fecha)) as Date;

LOAD

  TimeStamp(TimeStamp#(Fecha, 'YYYYMMDDhhmmssfff'), 'YYYY MM DD hh:mm:ss TT')as Fecha,

  Producto,

    "Categoría 1",

    "Categoría 2",

    "Categoría 3",

    Cantidad,

    "Ventas Netas",

    EN_CTA_CTE,

    Sucursal,

    Vendedor,

    "Costo Total",

    Proveedor,

    Cliente,

    "Condcion Comercial",

    Mes,

    Año,

    Día,

    Ciudad,

    Segmento

FROM [lib://prueba/Datos Reales.xlsx](ooxml, embedded labels, table is [Base de datos]);

[sunny_talwar · Accepted solution]

For the MonthYear field that you are creating, you can create it using one of the two approaches:

LOAD *,

  Year(Fecha) as Year,

    Month(Fecha) as Month,

    Time(Frac(Fecha)) as Time,

    Date(Floor(Fecha)) as Date,

    MonthName(Fecha) as MonthYear,

    Date(Fecha, 'MM-YYYY') as MonthYear1;

LOAD

  TimeStamp(TimeStamp#(Fecha, 'YYYYMMDDhhmmssfff'), 'YYYY MM DD hh:mm:ss TT')as Fecha,

  Producto,

    "Categoría 1",

    "Categoría 2",

    "Categoría 3",

    Cantidad,

    "Ventas Netas",

    EN_CTA_CTE,

    Sucursal,

    Vendedor,

    "Costo Total",

    Proveedor,

    Cliente,

    "Condcion Comercial",

    Mes,

    Año,

    Día,

    Ciudad,

    Segmento

FROM [lib://prueba/Datos Reales.xlsx](ooxml, embedded labels, table is [Base de datos]);

I personally like MonthName approach, but I have seen the other being use as well.