DATE FORMAT

[upaliwije]

Hi

In my data table date is recorded in the following way

17-May-2007 19:05:11

17-May-2007 19:07:36

17-May-2007 19:08:58

17-May-2007 19:09:38

17-May-2007 19:13:41

17-May-2007 19:23:46

I want to return the date without time as given below

17-May-2007

Pls help me to format this in my load script

[Gysbert_Wassenaar]

It depends on what's really in your database, string values or timestamp/datetime values.

In the first case you need to use something like Date(Floor(Timestamp#(MyDate,'DD-MMM-YYYY hh:mm:ss')),'DD-MMM-YYYY'). In the second case you can use Date(Floor(MyDate),'DD-MMM-YYYY')

[jonathandienst]

This is how to do this with date functions:

     Date(Floor(Date#(DateField, 'dd-MMM-yyyy hh:mm:ss')), 'dd-MMM-yyyy') As DateField

The Floor() is essential to produce a date, rather than a datetime value. The formatting function, Date() does NOT remove the time component.

[jagan]

HI,

Try like this

LOAD

*,

Date(Floor(Date#(DateField,'DD-MMM-YYYY hh:mm:ss')),'DD-MMM-YYYY') AS Date_New

FROM DataSource;

Regards,

Jagan.

[upaliwije]

I tried with all you ways. But I do not get the desired output

When I load the date with following command

LOAD *

PERIOD_TO

....

Output I get is   5/1/2013 12:00:00 AM

but when I load either of the following command no result is shown

DATE(FLOOR(Date#(POL_PERIOD_TO,'DD-MMM-YYYY')),'DD-MMM-YYYY') AS PERIOD_TO,

   Date(Floor(Timestamp#(POL_PERIOD_TO,'DD-MMM-YYYY hh:mm:')),'DD-MMM-YYYY')AS PERIOD_TO,

Pls help

[PrashantSangle]

Hi,

one more way

use subfield(datefield,'  ',1)

or

date(Floor(Timestamp#(datefield,'DD-M-YYYY hh:mm:ss')),'DD-MMM-YYYY')

Edit :

date(Floor(Timestamp#(datefield,'DD-M-YYYY hh:mm:ss TT')),'DD-MMM-YYYY')

Note : adjust your format as per data you have.

Regards

[jonathandienst]

That;s not what Gysbert suggested. You need to include the time formatting in the Date# function

Date(Floor(Date#(POL_PERIOD_TO, 'DD-MMM-YYYY hh:mm:ss')),'DD-MMM-YYYY') AS PERIOD_TO,

If that does not work, I suggest that you upload some sample dates.

[upaliwije]

I have attached my QV pls advise

[#9]

Hi Upali,

Instead of doing with date conversion, can you please try using below by using Index and left functions.

input:

load * inline [

date

17-May-2007 19:05:11

17-May-2007 19:07:36

17-May-2007 19:08:58

17-May-2007 19:09:38

17-May-2007 19:13:41

17-May-2007 19:23:46

];

output:

load left(date,Index(date,' ')-1) as  oo

Resident input;

Thanks,

Sreeman

[senpradip007]

Try like

LOAD *, date(date#(date_time, 'DD-MMM-YYYY hh:mm:ss'), 'DD-MMM-YYYY') as Date Inline [

date_time

17-May-2007 19:05:11

17-May-2007 19:07:36

17-May-2007 19:08:58

17-May-2007 19:09:38

17-May-2007 19:13:41

17-May-2007 19:23:46

];