Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
gnmq
Contributor III
Contributor III

Date field inconsistent

Hi,

 

I have difficulties on combining two tables with different date format

Table A: YYYYMMDD

Table B: MM/DD/YYYY HH:MM:SS TT

 

LOAD

DATE(FLOOR(DATEFIELD),'YYYYMMDD') AS DATEFIELD;

[TABLE_B]:

SELECT DATEFIELD FROM <DB>;

 

CONCATENATE(TABLE_A)

LOAD DATE(FLOOR(DATEFIELD),'YYYYMMDD') AS DATEFIELD

RESIDENT TABLE_B;

DROP TABLE TABLE_B;

 

The final table is showing table A's date as YYYYMMDD but table B's date values are in numeric format (5 digits).. May I know how to format this? Would appreciate your help on this. Thank you.

 

 

 

Labels (1)
1 Solution

Accepted Solutions
DavidM
Partner - Creator II
Partner - Creator II

You can either format table A to number Num(Datefield) or probably better solution convert table B to date:

Date(Datefield,'YYYYMMDD')

However, before you do that, check whether Table A and Table B are interpreted as dates, load table and in data model viewer and check whether the fields have tags $timestamp and $date. If not, you need to interpret them as date with Date# function.

View solution in original post

2 Replies
DavidM
Partner - Creator II
Partner - Creator II

You can either format table A to number Num(Datefield) or probably better solution convert table B to date:

Date(Datefield,'YYYYMMDD')

However, before you do that, check whether Table A and Table B are interpreted as dates, load table and in data model viewer and check whether the fields have tags $timestamp and $date. If not, you need to interpret them as date with Date# function.

Akshada
Partner - Contributor III
Partner - Contributor III

Hello,

I have replicated your scenario with inline table. Hope it will be helpful to you. Please check and confirm.

T1:
load *,
DATE(DATE#(Date,'YYYYMMDD'),'DD-MM-YYYY') as Date1;
load * Inline [
Date, Sale
20190822, 455
20190823, 435
20190915, 534
20191030, 635
];

T2:
Load *,
DATE(Date#(Date,'MM/DD/YYYY HH:MM:SS TT'),'DD-MM-YYYY') as Date1;
load * inline [
Date, Sale
01/25/2020 02:02:00 AM, 300
01/26/2020 07:05:00 AM, 545
01/28/2020 04:06:00 AM, 885
01/29/2020 06:10:00 AM, 656
];

Drop Field Date;

table will be auto concatenate. 

 

Output: 

date.PNG