locked
Computed column specification DATETIME to DATE RRS feed

  • Question

  • I have an Azure logic app which feeds a date time field from twitter into SQL. 

    I need a new permanent column that is just date only in the table.  Can I do this via computed column specification? 

    I want to keep the date time field so changing that is not an option and just a date is not available from the raw data.

    Would post an image but never seem to be verified....

    Thursday, December 13, 2018 11:48 AM

Answers

  • In the SQL database data type column it says 'datetime' 

    Output from Query looks like:

    2018-12-12 13:31:48.000 

    And I just want something like 12-12-2018 (UK format) in another column 

    Cheers :) 

    Then the previous CAST logic should work

    if you want it in UK format you can always format it in front end application

    Doing this in database would mean changing datatype to varchar which cause issues in date value manipulations. So better store them as date in the table and do formatting at front end

    If you dont have proper front end you can use CONVERT or FORMAT to do formatting in t-sql

    ALTER TABLE dbo.YourTable
        ADD DateOnly AS FORMAT(DateAndTime,'dd-MM-yyyy');

    or this

    ALTER TABLE dbo.YourTable
        ADD DateOnly AS CONVERT(varchar(10),DateAndTime,105);

    But that would be if you've no options to do at front end


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, December 13, 2018 12:37 PM

All replies

  • Yes, you can create a computed column with only the date based on a datetime column in the same row:

    ALTER TABLE dbo.YourTable
        ADD DateOnly AS CAST(DateAndTime AS date);

    Rather than post images, it's generally best to post T-SQL in your question. 


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

    Thursday, December 13, 2018 12:04 PM
    Answerer
  • That seemed to process but now when I try to query the table I get:

    Msg 241, Level 16, State 1, Line 1
    Conversion failed when converting date and/or time from character string. 

    Thursday, December 13, 2018 12:14 PM
  • That seemed to process but now when I try to query the table I get:

    Msg 241, Level 16, State 1, Line 1
    Conversion failed when converting date and/or time from character string. 

    what is the datatype of DateAndTime?

    If its character based, you need to show us the format in which it stores values currently so that we can suggest conversion logic to be applied ti make it into date


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, December 13, 2018 12:21 PM
  • In the SQL database data type column it says 'datetime' 

    Output from Query looks like:

    2018-12-12 13:31:48.000 

    And I just want something like 12-12-2018 (UK format) in another column 

    Cheers :) 

    Thursday, December 13, 2018 12:23 PM
  • In the SQL database data type column it says 'datetime' 

    Output from Query looks like:

    2018-12-12 13:31:48.000 

    And I just want something like 12-12-2018 (UK format) in another column 

    Cheers :) 

    Then the previous CAST logic should work

    if you want it in UK format you can always format it in front end application

    Doing this in database would mean changing datatype to varchar which cause issues in date value manipulations. So better store them as date in the table and do formatting at front end

    If you dont have proper front end you can use CONVERT or FORMAT to do formatting in t-sql

    ALTER TABLE dbo.YourTable
        ADD DateOnly AS FORMAT(DateAndTime,'dd-MM-yyyy');

    or this

    ALTER TABLE dbo.YourTable
        ADD DateOnly AS CONVERT(varchar(10),DateAndTime,105);

    But that would be if you've no options to do at front end


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, December 13, 2018 12:37 PM
  • First one worked for me; thanks v much
    Thursday, December 13, 2018 12:45 PM
  • In the SQL database data type column it says 'datetime' 

    Output from Query looks like:

    2018-12-12 13:31:48.000 

    And I just want something like 12-12-2018 (UK format) in another column 

    Be aware that a SQL Server date column has no format. It is internally stored as a 4-byte binary value. The formatted display value is controlled by the client application (SSMS in this case).

    The best practice is to use ISO 8601 date format literals (e.g. 'yyyy-mm-dd' or 'yyyymmdd') to avoid ambiguity in queries. Although the formatted date string computed column may address your needs, be aware that it will not provide the desired results for date range searches and ordering, and it requires more storage space.

    Another best practice is to store data using proper native types and perform display formatting in the presentation layer.


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


    Thursday, December 13, 2018 1:01 PM
    Answerer