Can't format date in Load Script

[#1]

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?

[ahaahaaha]

Hi,

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

[ahaahaaha]

You want to apply

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

[#4]

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]

can u share the sample data to test?

[#6]

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

[dberkesacn]

I would suggest this BLOG

The Date Function

Daniel

[Chanty4u]

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

[#9]

Syntax is fine.

[stigchel]

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