• where are you displaying this, in SSMS or in some SSRS report?
    you can convert the values to varchar with a case statement, but is that what you want?
    /*
    object_id    create_date    StringDate
    14675150    2017-06-08 11:46:34.517    2017-06-08 11:46:34
    16771167    2017-08-10 14:12:29.613    2017-08-10 14:12:29
    33435193    2016-12-13 15:05:33.830    2016-12-13 15:05:33
    38291196    2016-10-14 00:00:00.000    2016-10-14
    56387270    2016-11-15 14:31:42.447    2016-11-15 14:31:42
    71671303    2016-08-03 00:00:00.000    2016-08-03
    */
    DECLARE @SampleData TABLE([object_id] int,[create_date] datetime)

    INSERT INTO @SampleData
    SELECT '14675150','2017-06-08 11:46:34.517' UNION ALL
    SELECT '16771167','2017-08-10 14:12:29.613' UNION ALL
    SELECT '33435193','2016-12-13 15:05:33.830' UNION ALL
    SELECT '38291196','2016-10-14 00:00:00.00' UNION ALL
    SELECT '56387270','2016-11-15 14:31:42.447' UNION ALL
    SELECT '71671303','2016-08-03 00:00:00.00'

    SELECT *,
    CASE
    WHEN [create_date] = CONVERT(date,[create_date]) THEN CONVERT(varchar(10),[create_date],120)
    ELSE CONVERT(varchar(30),[create_date],120)
    END AS StringDate

    FROM @SampleData

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!