Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Change datetime format to date in SQL

Hi,

 

I was trying to change the datetime format to date in the SQL statement in order to lessen the M query due to connecting to a huge database.

Here is my code: convert(date, createTime, 101), but turned out error. What is the appropriate command for it?

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@Phil_Seamark,

 

The createtime column is in datetime format.

Only the cast(createtime as date) works for me. The result is in the format of datetime too.

datetime.JPG

 

Is that possible to direct convert it to date format without using query M?

View solution in original post

5 REPLIES 5
Phil_Seamark
Microsoft Employee
Microsoft Employee

What is the datatype for your createTime column in SQL?  Is it a Date-Time column or a character based data type (Char/NChar?)

 

you could try the following

 

CAST(createTime AS Date)

 

 

or

 

convert(date, createTime, 103)

or

 

TRY_CAST(createTime AS Date)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

@Phil_Seamark,

 

The createtime column is in datetime format.

Only the cast(createtime as date) works for me. The result is in the format of datetime too.

datetime.JPG

 

Is that possible to direct convert it to date format without using query M?

Date format is DateTime only set to midnight.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

That's obvious and is clearly not the question.

 

You are aware that in M there's a data type Date that removes the Time component completely? So why when in SQL you explicitly CAST DateTime as DATE PowerQuery is not picking it up as Date data type but instead is treating it as DateTime with 00:00:00 in the time part? It looks ugly and is confusing.

Any updates on this or do I have to explicitly FIX every date column in Power BI?  It's a complete pain when you've already specified it as a DATE data type in Microsoft SQL Server. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.