Date conversion

[brindlogcool]

I have a date field which is always showing as Text format even after using interpretation function Date#. The data source is Txt file.

I tried Date#()

I tried floor with Date#()

Still no use. But i had another txt file which has date function and it is working perfectly fine using the interpretation function Date#.

Not able to identify root cause for the issue. And more concern is when the function is working with one text file and not working with other,

i had through HIC posts related to date filed but nothing works. Is it anything related to encoding format.

[Alejandro_Hernández]

Could you post an example of your date values?

[Anonymous]

Hi Brindlogcool,

Try this:

Date#(Num('field')) if your data looks like YYYYMMDD

or

date(date#(DateField,'YYYY-MM-DD')) if your DateField looks like YYYY-MM-DD

I hope this will help you

[lawrenceiow]

as @Alejandro_Hernandez says, tt would be handy if you provided some examples of the dates as they appear in your txt file.

You've not mentioned it specifically but have you tried using date(date#())?

So if your DateField looks like 2015-03-27 you would use

  date(date#(DateField,'YYYY-MM-DD'))

[brindlogcool]

Attached the file for the reference. Had deleted all the details except the date

[lawrenceiow]

So, I presume you used    date(date#(mid(DateField,22,10),'MM/DD/YYYY'))    and that did not work?

[brindlogcool]

Thanks. Yes it didn't work. I checked the file format is ANSI. I am not sure do i need to do anything with this setting.

[maxgro]

load

  makedate(Right(row,4), left(row,2), mid(row,4,2)) as d;

LOAD

  left(KeepChar([@1:n],'/0123456789:'),10) as row

FROM

[03-25-2015.txt]

(fix, codepage is 1252)

where len(trim([@1:n]))>0;

another one

load

date(Date#(row, 'MM/DD/YYYYhh:mm:ss'), 'MM-DD-YYYY hhmmss') as d2

  ;LOAD

  left(KeepChar([@1:n],'/0123456789:'),18) as row

FROM

[03-25-2015.txt]

(fix, codepage is 1252)

where len(trim([@1:n]))>0;

[pokassov]

Can you check this code?

I copied the whole text file in "inline" load.

both fields work correctly

t1:

load * Inline [

textdata

-------Start of log-- 03/25/2015 13:16:13 --------

-------Start of log-- 03/25/2015 17:10:42 --------

-------Start of log-- 03/25/2015 18:54:32 --------

];

t1:

LOAD

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

  Timestamp(Timestamp#(data,'MM/DD/YYYY hh:mm:ss')) as datatime;

LOAD

mid(textdata,23,19) as data

Resident

  t1;

The result (regarding my settings):

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY h:mm:ss[.fff]';

[brindlogcool]

Are you able to get the same results from the attached text file without inline table?