Date Format returning null Value

[mgdotcom]

I've attempted to format datetime to Date (YYYY/MM/DD) without any success. All I get is a null value. I've attempted the below formatting methods:

1. Date(distribution_timestamp) as Date,
2. Date(Timestamp#(distribution_timestamp,'YYYY/MM/DD ')) as Date,
3. Date(Floor(Date#(Trim(distribution_timestamp), 'YYYY/MM/DD hh:mm:ss')), 'YYYY/MM/DD') as Date,
4. Date(distribution_timestamp,'YYYY/MM/DD') as Date
5. Date(Floor(Timestamp#(distribution_timestamp, 'YYYY/MM/DD hh:mm:ss')), 'YYYY/MM/DD') as Date,
6. Date(floor(distribution_timestamp,'YYYY/MM/DD ')) as Date,

None of which were successful. 

The Date formatting in the DB I am selecting from is as per the below:

2019-01-25 14:16:59.0000000

My Main tab has been set up as per the below:

SET ThousandSep=' ';
SET DecimalSep='.';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='R # ##0,00;R-# ##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='YYYY/MM/DD';
SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;
SET FirstMonthOfYear=1;
SET CollationLocale='en-ZA';

[florentina_doga]

field is text

 field=2019-01-25 14:16:59.0000000

use next expression in script

makedate(subfield(field,'-',1),subfield(field,'-',2),subfield(field,'-',-11)) as date

[florentina_doga]

sorry

makedate(subfield(field,'-',1),subfield(field,'-',2),subfield(field,'-',-1))

[mgdotcom]

I've applied 

makedate(subfield(distribution_timestamp,'-',1),subfield(distribution_timestamp,'-',2),subfield(distribution_timestamp,'-',-1)) AS DIS

The value is still null. 

[vhespinog]

Hi Mgdotcom

Try this

=Makedate(SubField(distribution_timestamp,'-',1),SubField(distribution_timestamp,'-',2),left(SubField(distribution_timestamp,'-',-1),2))

[sunny_talwar]

I think knowing the format in which QlikView tries to load the date is important... when you load your date without any transformation... do you see this? 2019-01-25 14:16:59.0000000. If you do, then try this

Date(Floor(Date#(Trim(distribution_timestamp), 'YYYY-MM-DD hh:mm:ss.fff')), 'YYYY/MM/DD') as Date,

[Brett_Bleess]

Mervin, did Sunny's last post work for you?  If so, please be sure to use the Accept as Solution button on his post to give him credit and confirm to others his solution worked.  If you are still working on things, let us know where things stand, so we can try to come up with further ideas.  About the best I have to try to help would be the following Design Blog link on Dates that may be somewhat useful.

https://community.qlik.com/t5/Qlik-Design-Blog/Why-don-t-my-dates-work/ba-p/1465849

https://community.qlik.com/t5/Qlik-Design-Blog/The-Date-Function/ba-p/1463157

Regards,
Brett

[mgdotcom · Accepted solution]

I ended of going with 

Date((Left(distribution_timestamp,10)),'YYYY/MM/DD') as Date

Not ideal but it did the job.