DATE FIELD, ERROR IN YEAR

[#1]

I have a table, one of my dimensions is a date field, but this is show 01/12/2323234 the year is showing 7 digitis. i have funtion date previsouly in the filed. DATE(FIELD, 'DD,MM,YYYY')

i need real information for every year

Thanks.

[sunny_talwar]

Can you share a screenshot of what you are seeing?

[#3]

hi, sunny, thanks for answer, here the field.

with date funtion

[Chanty4u]

try this

Load *,

Date(Date#( DATEFIELD, 'DD,MM,YYYY'),'DD/MM/YYYY')  as newDate

[#5]

Hi, thanks for answer, with this still happen the same thing!

[MindaugasBacius]

I cannot get the pattern.

The number value for today() date is 42527 = 2016.06.06.

I suggest you asking your Business program administrator for Technical reference of the database definitions or Entity diagrams. That may help.

[oscar_ortiz]

Question to ask is how the data is stored.  The values listed in your second mage do not make sense for a date field.  If it truly is a date field what format is the date stored in?  As mindaugasbacius‌ suggested talk to your DBA maybe they can shed some light on the field in question.

Good luck

Oscar

[settu_periasamy · Accepted solution]

Hi Kevin,

Date, seems to be Unix format. May be you can try like...

LOAD
Unix_Date,
Date((Unix_Date/60/60/24)+MakeDate(1970,01,01),'DD.MM.YYYY') as Date1,
date(floor(Unix_Date/ 86400 + 25569)) as Date2,
timestamp(Unix_Date/ 86400 + 25569) as TimeStamp,
time(frac(Unix_Date/ 86400 + 25569)) as Time;
LOAD * INLINE [
    Unix_Date
    1354401000
    1354402800
    1357441200
    1357441200
    1357686000
    1357767000
    1357635600
];

look this thread as well..  Convert unix time stamp to date and time

[#9]

settu_periasamy‌ THANK U SO MUCH. is a great solution, and the result was the expected!

[Anonymous]

Hi Kevin,

I have the below format Unix dates for which the logic is not working provided by you.

1519346684
1519347743
1519349266
1519349424
1519349948
1519350277
1519350278
1519350289
1519353152
1519353153
1519353263
1519353934
1519355949

Can you please check and tell me the right approach for this.

Thanks,

Rajesh.