/*object_id create_date StringDate14675150 2017-06-08 11:46:34.517 2017-06-08 11:46:3416771167 2017-08-10 14:12:29.613 2017-08-10 14:12:2933435193 2016-12-13 15:05:33.830 2016-12-13 15:05:3338291196 2016-10-14 00:00:00.000 2016-10-1456387270 2016-11-15 14:31:42.447 2016-11-15 14:31:4271671303 2016-08-03 00:00:00.000 2016-08-03*/DECLARE @SampleData TABLE([object_id] int,[create_date] datetime)INSERT INTO @SampleDataSELECT '14675150','2017-06-08 11:46:34.517' UNION ALLSELECT '16771167','2017-08-10 14:12:29.613' UNION ALLSELECT '33435193','2016-12-13 15:05:33.830' UNION ALLSELECT '38291196','2016-10-14 00:00:00.00' UNION ALLSELECT '56387270','2016-11-15 14:31:42.447' UNION ALLSELECT '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
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;
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)