Showing results for 
Search instead for 
Did you mean: 
Super User I
Super User I

SQL Native Query for extracting DATE portion

Hello Experts,


I have a a SQL table with a column called CreatedDate. The native data type is datetime (known through Table.Schema). It looks as following once I connect to the table  with following SQL custom statement





select [$Table].[CreatedDate]  as [CreatedDate]
     from [dbo].[X] as [$Table]


Now all I want is the column value to feature as only DATE without any time like following




after reading some blog posts I tried the following


select CONVERT(VARCHAR(10), [$Table].[CreatedDate], 110)  as [CreatedDate]
     from [dbo].[X] as [$Table]

Code 1


Outout 1Outout 1

select CAST ([$Table].[CreatedDate] as DATE) as [CreatedDate]
     from [dbo].[X] as [$Table]

Code 2



select CONVERT(DATE, [$Table].[CreatedDate])  as [CreatedDate]
     from [dbo].[X as [$Table]



The probelm here is CODE 1 turns the output to a Text value whereas CODE 2 and 3 keeps the value as DATE but brings the time too 12:00:00 AM.


Can I use a SQL code which helps me extract only the date part and yet the data type detected by PBI as DATE after the SQL execution.


Thank you in advance.

New Animated Dashboard: Sales Calendar

Community Support
Community Support

Re: SQL Native Query for extracting DATE portion

Hi @smpa01 ,


Actually, code2, code3 already suitable for your requirement.
It only extract date part from your datasource, you only need to change the column type to date to remove blank time part.(12:00:00 AM equal to 0:00:00)



Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!


Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors