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: 
Not applicable

Can't format date in Load Script

Hi guys,

I don't understand what is causing this problem. I have a date field which I extract from a SQL server using cast(date as date) function, which returns an ISO format YYYY-MM-DD.

In the Master Calendar autogeneration script, i'm trying to format the date like:

date(date#([Date], 'YYYY-MM-DD'),'DD/MM/YYYY') as Date.

I have a basic fact table, and a master calendar table for this particular date field, however, it is displaying in YYYY-MM-DD format. I've tried the above formula on the field in the fact table and in the master calendar, and it still displays incorrectly.

What's going on?

Capture.PNG

13 Replies
ahaahaaha
Partner - Master
Partner - Master

Hi,

In the figure you have in the first field a different calculation formula. Where Date#()?

ahaahaaha
Partner - Master
Partner - Master

You want to apply


Date(Date#([Date], 'YYYY-MM-DD'),'DD/MM/YYYY') as Date

Not applicable
Author

The first column was just me testing what would happen if I did it as a calculated dimension. I would expect the same output in the "Date" column, as I use the date# formula in the load.

Chanty4u
MVP
MVP

can u share the sample data to test?

Not applicable
Author

That's what I am doing in the load script, and that is generating the second column.

dberkesacn
Partner - Creator III
Partner - Creator III

I would suggest this BLOG

The Date Function

Daniel

Chanty4u
MVP
MVP

what is your database date column name? 

it should be case sensitive

date(date#([date], 'YYYY-MM-DD'),'DD/MM/YYYY') as Date.

you mentioned as " Date"  so is that issue? could you please check?  as syntax is correct

Not applicable
Author

Syntax is fine.

Capture.PNG

stigchel
Partner - Master
Partner - Master

What does your SET DateFormat look like in the load script? 

When it is

SET DateFormat=' YYYY-MM-DD';

Change to

SET DateFormat='DD/MM/YYYY';