Date format functions not working

[#1]

Hi

I am completely new to Qlikview (using personal edition 9.00.7469.8 SR4). Busy going through the end user tutorial and there is an [Invoice Date] field loaded as well as fields where the year and month are extracted from [Invoice Date] - IE: year([Invoice Date]) as Year and month([Invoice Date]) as Month;

The problem is that the year and date function do not work on the data that is extracted from a text file as the date format is USA m/d/yy and my regional settings are yy/mm/dd. I have tried changing my regional settings to match the USA format of the data and nothing. I even try to use the date() and date#() functions but they seem to have no effect whatsoever on this field. As a result the year and month functions display 0 results / rows.

Any ideas how I can work around this ?

Here is the section of script:

LOAD
[Invoice Date],
Year([Invoice Date]) as Year,
Month([Invoice Date]) as Month,

I have tried:

date#([Invoice Date], 'yyyy-mm-dd') amongst other things

but this seems to have no effect on the output at all.

Please help.

[#2]

Hi

Are you applying the Date#() function in the script?

Thy doing it from the front end (Expressions). On a specific object.

Thanks,

[spsrk_84]

Hi,

Check the system date settings also,if they are not in correct format as required change them to required and even change the default settings

in Qlikview script window also..

Regards,

Ajay

[#4]

Yes I have tried applying it in the script.

I have just tried to apply it using an expression - although I'm not sure if the expression I am using is correct: I tried to enter Date#([Invoice Date], 'YYYY.MM.DD') as well as =Date#([Invoice Date], 'YYYY.MM.DD') into the expression field under the sort tab but no change occurs. There does not seem to be any other place to enter an expression in a listbox.

I have also tried changing my regional settings (Using Windows XP) to USA (as mentioned in first post) but it makes no difference to any of these functions working.

Could it possibly be a problem with not realising the data type is a date field and instead reading it as text ? It was imported from a text document.

[#5 · Accepted solution]

Hi!

Do you have these variables in your script?

SET

SET

You can change "DateFormat" with the format of your dates. May be this will resolve your problem. DayNames = 'Lun;Mar;Mer;Jeu;Ven;Sam;Dim' ; TimeFormat = 'h:mm:ss TT' ;

SET

DateFormat = 'M/D/YYYY' ;

SET

TimestampFormat = 'D/M/YYYY h:mm:ss[.fff] TT' ;

SET

MonthNames = 'Jan;Fev;Mar;Avr;Mai;Juin;Juil;Aou;Sep;Oct;Nov;Dec' ;

[#6]

Thanks Aziz. That was the trick. Silly me I should have spotted that. Obviously Qlikview uses regional settings when initially setting up the script but once you save the script it won't help to change the regional settings on the OS.