DateTime Field Hide time if it is midnight

  • gtjr92

    Valued Member

    Points: 69

    I need a query to hide the time from a datetime field if the time is midnight.
    I tried a case statement but that didn't work any ideas?

    Thanks

  • Luis Cazares

    SSC Guru

    Points: 183562

    gtjr92 - Thursday, September 14, 2017 1:54 PM

    I need a query to hide the time from a datetime field if the time is midnight.
    I tried a case statement but that didn't work any ideas?

    Thanks

    What format do you want? Why are you doing this in SQL Server instead of doing it in the front end?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Lowell

    SSC Guru

    Points: 323391

    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!

  • Luis Cazares

    SSC Guru

    Points: 183562

    Using Lowell's sample data, here are 2 other options.

    SELECT [create_date],
       LEFT( CONVERT( varchar(24), [create_date], 121),
          CASE WHEN [create_date] = DATEADD(dd, DATEDIFF(dd, 0, [create_date]), 0)
           THEN 10
           ELSE 23
          END),
       REPLACE( CONVERT( varchar(24), [create_date], 121), '00:00:00.000', '')
    FROM @SampleData;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • ZZartin

    SSC-Dedicated

    Points: 30355

    Is your requirement really only for exactly midnight?  The options above work for exactly midnight as in 2016-12-13 00:00:00.000, what about 2016-12-13 00:00:00.003?

  • Thom A

    SSC Guru

    Points: 98326

    Nevermind me, miss read 🙂

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • gtjr92

    Valued Member

    Points: 69

    Thanks everyone I ended up using Luis Example

    SELECT [create_date],
      LEFT( CONVERT( varchar(24), [create_date], 121),
      CASE WHEN [create_date] = DATEADD(dd, DATEDIFF(dd, 0, [create_date]), 0)
       THEN 10
       ELSE 23
      END)

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply