issues with showing date with time format in sql

Question

I am trying to show only date here and not the time but whatever i do still is showing the time like this format 4/4/2014 12:00:00 AM. I would like to remove the time portion of the date and only show the date. What am i doing wrong here? here is my sql

 SELECT
 DATEADD(DD, 
  CONVERT(INT, (DATEDIFF(DD, '1/1/1900', t.DT)/7)) * 7, '1/1/1900') [WeekBeginDate],                                 
  SUM(HOURS) AS TOTAL_HOURS
   FROM [DA2].[PMO].[TASK_TIME_TRACKER] t
    WHERE UID = 'John07'
    AND DT >= DATEADD(WEEK, -4, GetDate())
     GROUP BY CONVERT(INT, DATEDIFF(DD, '1/1/1900', t.DT)/7)

Daniel E. · Answer 1 · 2014-05-05T18:38:30.550

0

DATEADD(DD, CONVERT(INT, (DATEDIFF(DD, '1/1/1900', CONVERT(DATE,t.DT))/7)) * 7, '1/1/1900')

You also need to change your group by expression to include the conversion. that will group each day together regardless of time, to display just the time you may need to wrap the whole line in an outer convert.

GROUP BY CONVERT(INT, DATEDIFF(DD, '1/1/1900', CONVERT(DATE,t.DT))/7)

What are you using to display the results? if it is SSRS you can change the format on the cell to date to drop the 0's that the timestamp would display.

edited May 05 '14 at 18:38

answered May 05 '14 at 17:52

Daniel E.

2,029
3
22
28

thanks Daniel, i am getting a syntax error when i tried that. – moe May 05 '14 at 17:59
what does the error say? what version of SQL are you running? – Daniel E. May 05 '14 at 18:09
sql 2008 R2, it does not like this line: SUM(HOURS) AS TOTAL_HOURS – moe May 05 '14 at 18:28
see edit, what is the exact error message?? – Daniel E. May 05 '14 at 18:49

issues with showing date with time format in sql

1 Answers1