DateTime Field Hide time if it is midnight

Question

DateTime Field Hide time if it is midnight

gtjr92

SSC Journeyman

Points: 89
More actions
September 14, 2017 at 1:54 pm

#328392

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

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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: 323518 More actions · Answer 2

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: 183706 More actions · Answer 3

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: 30920 More actions · Answer 4

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: 99130 More actions · Answer 5

Nevermind me, miss read 🙂

Thom~

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

gtjr92 SSC Journeyman Points: 89 More actions · Answer 6

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)