0

I am trying to convert the datetime output into a date only and i would like to have it in this format:

mm/dd/yyyy

what am i doing wrong here:

    select 
    DATEADD(DAY,  CONVERT(INT,(DATEPART(WEEKDAY, dt) - 1)) * -1,dt) as [WeekBeginDate],
    SUM(hours) AS TOTAL_HOURS
    from myTable
    where
    uid = 'myUID' and dt >= CAST(DATEADD(WEEK,-5,DATEADD(DAY,(DATEPART(WEEKDAY, GETDATE()) - 1) * -1,GETDATE())) AS date) group by DATEADD(DAY,  CONVERT(INT,(DATEPART(WEEKDAY, dt) - 1)) * -1,dt) order by 1
moe
  • 5,149
  • 38
  • 130
  • 197
  • Do U want to convert field `dt` to format `mm/dd/yyyy`? – Roman Marusyk Nov 05 '15 at 22:14
  • Possible duplicate of [Best approach to remove time part of datetime in SQL Server](http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server) – Roman Marusyk Nov 05 '15 at 22:17

1 Answers1

1

You can use format 101 for convert:

select convert(varchar(10), dt, 101)

If you want the week begging date using your expression:

select convert(varchar(10), DATEADD(DAY,  CONVERT(INT,(DATEPART(WEEKDAY, dt) - 1)) * -1,dt), 101) as [WeekBeginDate]
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786