Date Format

[danielnevitt]

Hi,

I have a date field named ReportDate that is in format DD/MM/YYYY hh:mm:ss.

How can I format this to DD/MM/YYYY on my load statement?

Thanks

Daniel

[Chanty4u]

try

loiad *,

Date(Date#(Datefield,,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY')   as newdate

resident table;

[Chanty4u]

check sample:

a:

LOAD * Inline [

date

11/02/1991 01:12:55

];

Result:

load *,

Date(Date#(date,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY')   as newdate

Resident a;

[sunny_talwar]

Good links to look at for issues related to dates:

Why don’t my dates work?

Get the Dates Right

[sunny_talwar · Accepted solution]

Another thing I would suggest is to use a Floor function to truncate the time portion of the date

Date(Floor(DateField)) as Date

[marcus_sommer]

To get a real date from a timestamp you need to cut the time-part, maybe in this way:

date(floor(ReportDate), 'DD/MM/YYYY')

and if ReportDate isn't regocnized as timestamp you need to convert it per timestamp#() like:

date(floor(timestamp#(ReportDate, 'DD/MM/YYYY hh:mm:ss')), 'DD/MM/YYYY')

- Marcus