Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Change full date to day, month and year

I faced a problem to separate the date to day, month, year. because the input in the sql database is wrong.

Untitled.png

I try this expression: =Year(docdate), but it doesn't work how can i fix this problem and view true date?

Regards,

1 Solution

Accepted Solutions
Kushal_Chawda

year(date#(Date,'YYYYMMDD')) as Year

month(date#(Date,'YYYYMMDD')) as Month

monthname(date#(Date,'YYYYMMDD')) as MonthYear

View solution in original post

7 Replies
Anonymous
Not applicable
Author

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

Chanty4u
MVP
MVP

chk below link

Date Format

Kushal_Chawda

year(date#(Date,'YYYYMMDD')) as Year

month(date#(Date,'YYYYMMDD')) as Month

monthname(date#(Date,'YYYYMMDD')) as MonthYear

Mark_Little
Luminary
Luminary

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

Anonymous
Not applicable
Author

Agreed with Mark,

You can try it by formatting as well like Kush suggested

prieper
Master II
Master II

DATE(DATE#(mydate, 'YYYYDDMM'))

Peter