Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I faced a problem to separate the date to day, month, year. because the input in the sql database is wrong.
I try this expression: =Year(docdate), but it doesn't work how can i fix this problem and view true date?
Regards,
year(date#(Date,'YYYYMMDD')) as Year
month(date#(Date,'YYYYMMDD')) as Month
monthname(date#(Date,'YYYYMMDD')) as MonthYear
try sth like this at script level:
=mid(docdate,1,4) as YEAR
mid(docdate,5,6) as Month
mid(docdate,7,8) as Day
chk below link
year(date#(Date,'YYYYMMDD')) as Year
month(date#(Date,'YYYYMMDD')) as Month
monthname(date#(Date,'YYYYMMDD')) as MonthYear
Hi there,
Balraj approach should give you what you need, the problem seems to be with QlikView does not recognise your field as a date. So another approach is to format it as a date field first, then you could use the Year() function.
Mark
When loading the data from your DB table, use a transformation like this when creating a QlikView field:
...Date#(docdate, 'YYYYMMDD') AS DocDate, ...
The DocDate field will now contain a regular (binary) date value that can be converted using the standard QlikView Date & Time functions. Note: this is not acceptable SQL code. Use this in a Preceding LOAD.
If you want to set the date format right, embed this call in a Date formatting function, like:
...Date(Date#(docdate, 'YYYYMMDD'), 'DD/MM/YYYY') AS DocDate, ....
If the format parameter corresponds to the default date format you have set in your script (see the SET statements at the top), then you can omit the format parameter.
Best,
Peter
Agreed with Mark,
You can try it by formatting as well like Kush suggested
DATE(DATE#(mydate, 'YYYYDDMM'))
Peter