locked
SQL storage of date not working RRS feed

  • Question

  • I have some data coming in from Twitter as date/time.  I have tried to convert this into date see pic attached.  This works but when it enters powerBI it is not a date format... it is text.  I am not then able to change it in powerBI.  How do I change it in SQL?

    https://imgur.com/a/qhCxTtR

    Thanks 

    Tuesday, February 5, 2019 9:19 AM

Answers

  • Try adding a strongly-typed computed column of type date:

    ALTER TABLE dbo.YourTable
        ADD CreatedAtDate AS CAST([Created at] AS date);


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Tuesday, February 5, 2019 11:13 AM
    Answerer

All replies

  • Cannot see the picture  you posted. Can provide some examples?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 5, 2019 10:57 AM
  • The link works when I checked just now  using Chrome.  It comes in from twitter as date/time then I have used a computed column of (format([Created at],'dd-MM-yyyy')) to change it to date only.  

    However, upon connection to powerBI the date only comes through as text.  Format looks right but it's defined as text and not a date.  This is then a problem as graphs etc with time don't work correctly.

    Thanks 

    Tuesday, February 5, 2019 11:00 AM
  • It comes through as text since the FORMAT function returns just that: text (nvarchar). If you want the client to handle it as some of the datetime types, then don't convert it to text. You can't have the cake *and* eat it. :-) The datetime types has no intrinsic format, it is the client application that does the formatting.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, February 5, 2019 11:04 AM
  • Ok... so focusing on the solution then?  How do I change the incoming date/time to be just date in another column pleae?  Thanks 
    Tuesday, February 5, 2019 11:05 AM
  • Try adding a strongly-typed computed column of type date:

    ALTER TABLE dbo.YourTable
        ADD CreatedAtDate AS CAST([Created at] AS date);


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Tuesday, February 5, 2019 11:13 AM
    Answerer
  • Thanks Dan this is great.  I can't see it as a field in my SQL design but it fires through to PowerBI in the correct format.  Cheers!
    Tuesday, February 5, 2019 11:34 AM