sql server - How to get week formatted string in sql query -
i have following sql query (sql server 2008):
select sum(data.freq) freq,data.week week ( select count(daterequested) freq, datepart(wk,daterequested) week, daterequested request ma join contracts mc on (mc.uid= ma.uid) join groups og on og.groupuid = mc.groupuid join member m on (m.memberuid = mc.memberuid) daterequested between dateadd(month,-1,getdate()) , getdate() , isdeleted = 0 group datepart(wk,daterequested),daterequested --order daterequested asc ) data group data.week
the result table following data:
instead of showing week number show week formatted following:
mm/dd
mm = month , dd day week starts.
it great if can format starting first day of week, middle slash, last day of week , month: example: 11-17/04 (april 11 17), etc.
here final table get:
any clue?
in case needs found solution, maybe not best works.
select sum(data.freq) freq, data.week week, cast(data.weekstart varchar) + '-' + cast(data.weekend varchar) + '/' + cast(data.monthend varchar) formatweek ( select count(daterequested) freq, datepart(wk,daterequested) week, datepart(dd,dateadd(dd, -(datepart(dw, daterequested)-1), daterequested)) weekstart, datepart(dd,dateadd(dd, 7-(datepart(dw, daterequested)), daterequested)) weekend, datepart(mm,dateadd(dd, 7-(datepart(dw, daterequested)), daterequested)) monthend, daterequested requestma join contracts mc on (mc.uid= ma.uid) join groups og on og.groupuid = mc.groupuid join member m on (m.memberuid = mc.memberuid) daterequested between dateadd(month,-1,getdate()) , getdate() , isdeleted = 0 group datepart(wk,daterequested),daterequested --order daterequested asc ) data group data.week,data.weekstart,data.weekend,data.monthend
Comments
Post a Comment