topic Re: Date Format returning null Value in QlikView

Date Format returning null Value

mgdotcom — Sat, 16 Nov 2024 19:51:18 GMT

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

Re: Date Format returning null Value

florentina_doga — Mon, 21 Oct 2019 09:09:20 GMT

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

Re: Date Format returning null Value

florentina_doga — Mon, 21 Oct 2019 09:10:34 GMT

sorry

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

Re: Date Format returning null Value

mgdotcom — Mon, 21 Oct 2019 09:35:34 GMT

I've applied

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

The value is still null.

Re: Date Format returning null Value

vhespinog — Mon, 21 Oct 2019 15:05:43 GMT

Hi Mgdotcom

Try this

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

Re: Date Format returning null Value

sunny_talwar — Mon, 21 Oct 2019 15:33:06 GMT

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,

Re: Date Format returning null Value

Brett_Bleess — Wed, 30 Oct 2019 14:15:55 GMT

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

Re: Date Format returning null Value

mgdotcom — Wed, 30 Oct 2019 14:19:24 GMT

I ended of going with

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

Not ideal but it did the job.