cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

 

Capture.JPG

 

 

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 1Outout 1

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

Code 2

 

Output2Output2

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

Code3

Output3Output3

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


1 REPLY 1
Highlighted
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)

 

Regards,

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

Announcements
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!

secondImage

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