Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
mgdotcom
Contributor III
Contributor III

Date Format returning null Value

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';

 

Labels (4)
1 Solution

Accepted Solutions
mgdotcom
Contributor III
Contributor III
Author

I ended of going with 

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

 

Not ideal but it did the job. 

View solution in original post

7 Replies
florentina_doga
Partner - Creator III
Partner - Creator III

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
Partner - Creator III
Partner - Creator III

sorry

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

mgdotcom
Contributor III
Contributor III
Author

I've applied 

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

The value is still null. 

vhespinog
Contributor III
Contributor III

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
Employee
Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
mgdotcom
Contributor III
Contributor III
Author

I ended of going with 

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

 

Not ideal but it did the job.