Date; From 'DD-MMM-YYYY hh:mm:ss TT' to DD-MM-YYYY

[#1]

Hello,

I have a database with the date put in as: 'DD-MMM-YYYY hh:mm:ss TT',

I only want to have the DD-MMM-YYYY, the time I don't care about. So I want to lose the time (hh:mm:ss)

It's probably a simple solution, but I couldn't find it.

thanks in advance

[marcos]

Hi,

this expressions could help you

table:

Load

DateField,

Date(Floor(DateField)) as NewDate,

Time(Frac(DateField)) as NewHour

From tab1

;

[CELAMBARASAN]

Hi,

     Try with this

     Date(Date#(DateField,'DD-MM-YYYY hh:mm:ss TT'),'DD-MM-YYYY')

Celambarasan

[Anonymous]

Hi Gerbrand,

That will be:

date(FIELD, 'DD-MMM-YYYY')

Good luck.

Regards,

Henco

[#5]

thanks for the fast reactions, but I still didn't get it to work.. I get errors...

I'll post my script here (what I had originaly) and maybe some1 can tell where to add the new part, your help is much appreciated.

ODBC CONNECT TO [MS Access Database;DBQ=GO_SALES\go_sales.accdb];

RETURN_DATE:

SQL SELECT DISTINCT

`RETURN_DATE`        as            RETURN_DATE_date

FROM `RETURNED_ITEM`;

Join (RETURN_DATE)

LOAD

    RETURN_DATE_date,

    Year(RETURN_DATE_date)                as        RETURN_DATE_YEAR_nr,

    Trim(Month(RETURN_DATE_date))         as         RETURN_DATE_MONTH_name

Resident RETURN_DATE;

Join (RETURN_DATE)

LOAD * INLINE [

    RETURN_DATE_MONTH_name, RETURN_DATE_MONTH_nr

    jan, 1

    feb, 2

    mrt, 3

    apr, 4

    mei, 5

    jun, 6

    jul, 7

    aug, 8

    sep, 9

    okt, 10

    nov, 11

    dec, 12

];

[jagan]

Hi,

Try this script.

RETURN_DATE:

LOAD

     *,

     Month(RETURN_DATE_date) AS RETURN_DATE_MONTH_name,

     Month(RETURN_DATE_date) * 1 ASRETURN_DATE_MONTH_nr,

     Year(RETURN_DATE_date) AS RETURN_DATE_YEAR_nr;

SQL SELECT DISTINCT

Date(Date#('RETURN_DATE','DD-MM-YYYY hh:mm:ss TT'),'DD-MM-YYYY') AS RETURN_DATE_date

FROM `RETURNED_ITEM`;

Hope this helps you.

Regards,

Jagan.

[Anonymous]

Hi,

RETURN_DATE

LOAD *,

     date(RETURN_DATE, 'DD-MM-YYYY') as RETURN_DATE

FROM RETURNED_ITEM;

Kind regards,

Henco

[CELAMBARASAN]

Hi,

     Check with this

ODBC CONNECT TO [MS Access Database;DBQ=GO_SALES\go_sales.accdb];

RETURN_DATE:

Load Distinct

     Date(Date#(RETURN_DATE_date,'DD-MM-YYYY hh:mm:ss TT'),'DD-MM-YYYY') as RETURN_DATE,

     Year(RETURN_DATE_date)                as        RETURN_DATE_YEAR_nr,

     Month(RETURN_DATE_date)*1         as         RETURN_DATE_MONTH_nr;

SQL SELECT DISTINCT

`RETURN_DATE`        as            RETURN_DATE_date

FROM `RETURNED_ITEM`;

LOAD * INLINE [

    RETURN_DATE_MONTH_name, RETURN_DATE_MONTH_nr

    jan, 1

    feb, 2

    mrt, 3

    apr, 4

    mei, 5

    jun, 6

    jul, 7

    aug, 8

    sep, 9

    okt, 10

    nov, 11

    dec, 12

];

/*    This all don't need

Join (RETURN_DATE)

LOAD

    RETURN_DATE_date,

    Year(RETURN_DATE_date)                as        RETURN_DATE_YEAR_nr,

    Trim(Month(RETURN_DATE_date))         as         RETURN_DATE_MONTH_name

Resident RETURN_DATE;*/

Hope it helps

Celambarasan

[magavi_framsteg]

Hi gerbrand, hencovanee and Celambarasan.

"

RETURN_DATE:

LOAD *,

    date(RETURN_DATE, 'DD-MM-YYYY') as RETURN_DATE

FROM RETURNED_ITEM;

"

Unfortunately the above lines of code won't give you the date part of the field without the hh:mm part.

If the date field contains the hh:mm part, date(RETURN_DATE, 'DD-MM-YYYY') will still contain the hh:mm part in the field.

The field will be displayed without the hh:mm part, but it still contains hh:mm.

The correct thing is either floor or left(5).

My sample code:

original:

LOAD * INLINE [

    original

    2012-02-02 08:32:21

    2012-02-01 09:35:52

];

yyyymmdd:

load

date(original, 'YYYY-MM-DD') as original_yyyymmdd

,date(floor(original), 'YYYY-MM-DD') as original_yyyymmdd_floor

,date(left(num(original), 5), 'YYYY-MM-DD') as original_yyyymmdd_left

resident original;

Then throw a listbox for each field and set the number format to Timestamp.

Then you clearly see that date(original, 'YYYY-MM-DD') still keeps the hh:mm information.

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.