0

I need some help on my SQL. How can I can add day of the week and calculate how many hours were worked on that day. 

    SELECT CONVERT(date,startTimeStamp) AS DATE
      ,CONVERT(time,[startTimeStamp])AS StartTime
      ,CONVERT(time,[endTimeStamp])AS EndTime
      ,CONVERT(time,[startTimeStamp])+ CONVERT(time,[endTimeStamp])
  FROM [TaskManagementSystem_DB].[dbo].[Timesheet_entry]
Herbert
  • 5,698
  • 2
  • 26
  • 34

1 Answers1

1

IN MSSQL, this should work

   SELECT CONVERT(date,startTimeStamp) AS DATE
      , datename(dw,CONVERT(date,startTimeStamp)) as dayOfWeek
      ,CONVERT(time,[startTimeStamp])AS StartTime
      ,CONVERT(time,[endTimeStamp])AS EndTime
      ,CONVERT(time,[startTimeStamp])+ CONVERT(time,[endTimeStamp])
  FROM [TaskManagementSystem_DB].[dbo].[Timesheet_entry]
Lance
  • 3,193
  • 2
  • 32
  • 49
  • You can also use weekday see http://stackoverflow.com/questions/1110998/get-day-of-week-in-sql-2005-2008 – Lance Sep 17 '12 at 19:24
  • one thing i want to ask how I can combine Date and dateOfWeek in one column –  Sep 17 '12 at 19:37
  • ask in another question. but it sounds like you want to do something for a report to a customer, I would do that in a report builder. – Malachi Sep 17 '12 at 19:58
  • I agree this should be a separate question but since dateName is a varchar you could convert your date to a varchar and just concatenate the two strings – Lance Sep 17 '12 at 20:08
  • http://stackoverflow.com/questions/12467872/how-to-combine-two-data-columns#comment16770982_12467872 –  Sep 17 '12 at 22:23