Can we and how to change DataType of TO_DATE and FROM_DATE in the projects

[adpaiks]

Hi

Need help with this query: 

The two attributes: TO_DATE and FROM_DATE are added to every sat table in compose. But we cant see these attributes in the model.

Is there any way of editing the datatype for example from datetime(6) to datetime(3) on DV layer? I could not see TO_DATE in the mappings also.

Problem is: these attributes are getting loaded from attributes from other attributes of the stage tables which are datetime(3) and the values are getting changed when they copy from datetime(3) to datetime(6). 

'2022-10-04 00:54:52.653' gets changed to '2022-10-04 00:54:52.653333' in DV and when these values are compared in the ETL sets for > or < operations it fails to compare as '2022-10-04 00:54:52.653' is taken as less than '2022-10-04 00:54:52.653333' in reality its the same value 

Thanks.

[ajaykakkar93]

hi,

use the function floor in backend

example:
date(floor(from_Date),'DD/MM/YYYY') as from_Date

&

date(floor(to_Date),'DD/MM/YYYY') as to_Date

[Nanda_Ravindra · Accepted solution]

@adpaiks  The TO_DATE and FROM_DATE attributes are added by Compose to the DWH tables, so you won't be see those attributes in the model.

FYI: The model represents the entities from the landing zone table of the source database

What type of database are you using ? I checked this in SQL server and I see that as datetime(6) - see below

Could you send us the screenshot of the table definition where you see the  datetime(3) ? Like the stage and the original table

We can modify those fields in the instructions, but we don't recommend doing that without knowing the use case as this can result in the wrong data in the DV and DM.

[adpaiks]

Thanks for your reply @Nanda_Ravindra 

The DB is Synapse, ya the datatype is datetime2(7)

we have mapped a field from the source(stage) which to from_date and this field is datetime(3).

If I check the ETL set, it uses below in the insert into of the TSTG table:

COALESCE([_LAST_TOUCHED_DT_UTC], CONVERT(DATETIME2, '&&2', 120)) [FROM_DATE]

when this function is used: 

2022-10-03 21:52:08.877 gets changed to 2022-10-03 21:52:08.876667 --> this value is inserted in the from and to dates of the DV as loads are done.

Where there is time to load this to MART tables, the comparison of picking the latest record fails and we dont the latest record populated to mart, because in sql for loading mart below > comparison is true : 

2022-10-03 21:52:08.877 > 2022-10-03 21:52:08.876667 --> this is true 

essentially its the same value thats coming from source

Ideally this should have inserted as 

2022-10-03 21:52:08.877 = 2022-10-03 21:52:08.877000

[Nanda_Ravindra]

@adpaiks could you please open a support case so we can check it in detail and provide the right solution/answer or a workaround if any?

Thanks,

Nanda

[adpaiks · Accepted solution]

Ok, will do.

I have worked around the issue by mapping another date to from_date whose data type is datetime(7)

Thanks.