SQL Native Query for extracting DATE portion

[smpa01]

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

CreatedDate

6/1/2016

7/14/2016

2/18/2016

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 1

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

Code 2

Output2

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

Code3

Output3

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.

[v-shex-msft]

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)

Regards,

Xiaoxin Sheng