Can we change column type in Fabric SQL Endpoint ?

[KenMann]

Hi guys,

I have CSV file that I uploaded into my Lakehouse, soon after by using drag and drop to Tables, I would have 1 table created with the same structure as the CSV. It is actually a CSV contain Date table. However when I take a look at the table structure, the column has no Date type column even with the field contain date.

Can we change it ? or can we add new column based on the exisiting column and lets say I want to new column to have Date type.

Firstly I though Lakehouse SQL Endpoint is the best place to do all that, but turns out I cannot find any button or function to support my plan.

Any other place I can do that ? or how to have a column with Date type when we have table with source is CSV ?

Thanks

[GilbertQ]

Thanks for letting us know.

[GilbertQ]

Hi @KenMann 

Because a CSV is just a text file, when creating a table in the Lakehouse it cannot determine what the column data types are.

One way to do it when querying via the SQL End Point is you can use the following code to cast it on the fly as shown below.

Select 
CAST(FullDateAlternateKey as Date) as Date
from dbo.MyTableName

NOTE: The SQL Endpoint is case sensitive for your column names so make sure to spell them exactly the same. 

[KenMann]

Hi,

Yes, this what I'm thinking as well. But may I know how to do it in SQL Endpoint ? 

Are you referring to that button "New sql query" and write the CAST over there like this ?

But, as this is just a query, how this will alter the table it self which then can be used in my data set outside (Power BI Dekstop)

Thanks,

[GilbertQ]

Hi @KenMann 

When you use the Power Query data source from "SQL Server" you can insert a query, which you can use from your screenshot above, which will then bring the data in as Date.

[KenMann]

I'm sorry, but I'm not quite understand what is the meaning by 'data source from "SQL Server" ', since my datasource is from CSV. And by looking at this resource : T-SQL surface area - Microsoft Fabric | Microsoft Learn

There are these limitation in SQL Endpoint:

[KenMann · Accepted solution]

Hi,

I'm finally manage to make the column as Date. Figure it out that I better not using "Drag and Drop" on the csv file to my Lakehouse table. Instead doing the Get Data feature so I can manually change the column inside PowerQuery, before the table created.

Issue solved. Thanks.