Need a query help on a date column

• malavika.ramanathan

  SSCrazy

  Points: 2790

  More actions

  July 20, 2010 at 12:39 am

  #235511

  Hi All,

  I have to make a report for which i have sales on y-axis and weeks on x-axis.

  For the x-axis data i need to use a datetime column in my table.

  Now, how can i segregate the entire column as weeks ?

  Let me know if more info is required.

  thanks,

  malavika.

• prashant.bhatt

  SSC-Addicted

  Points: 476

  More actions

  July 20, 2010 at 1:07 am

  #1195314

  You can use the last date of each week on the X-axis...

  Prashant Bhatt
  Sr Engineer - Application Programming

• malavika.ramanathan

  SSCrazy

  Points: 2790

  More actions

  July 20, 2010 at 2:49 am

  #1195361

  hi,

  thanks for reply. could you please tell me how can i write a query to get that?

  or is there any other way to get the weeks?

  my time column in DB is like :

  time_uploaded

  2009-05-31 23:13:09.140

  2009-05-31 23:13:26.240

  2009-05-31 23:13:58.397

  2009-05-31 23:14:09.143

  2009-05-31 23:14:20.177

  i must group these rows as so many weeks. say if i have one year's data in the table then i must get 52 weeks.

  so i will be plotting 52 weeks on my x-axis.

  thanks.

  malavika.

• ramu.valleti

  SSCrazy

  Points: 2229

  More actions

  July 20, 2010 at 4:01 am

  #1195384

  You can use DATEPART function (eg: DATEPART(ww, time_uploaded)).

  Ramu
  No Dream Is Too Big....!

• prashant.bhatt

  SSC-Addicted

  Points: 476

  More actions

  July 20, 2010 at 4:01 am

  #1195385

  You can use the following query to get the last date of the week..

  select dateadd(dd,7-datepart(dw,getdate()),getdate())

  just replace the getdate() with the date column and group the data based on this last date column...

  Prashant Bhatt
  Sr Engineer - Application Programming