-2

I am stumbling with SQL …see if you can help with 5 and 6

Business Need

  1. I have myDate
  2. I want SQL to give me week number for myDate
  3. I want SQL to return week start date of this year for myDate
  4. I want SQL to return week end date of this year for myDate
  5. (Need help)I want SQL to return week start date of LAST YEAR for myDate
  6. (Need help)I want SQL to return week end date of LAST YEAR for myDate

Comments on Data

  1. Date provided
  2. Date falls in week 19 of 2014
  3. Sunday of week 19 of 2014
  4. Saturday of week 19 of 2014
  5. Sunday of week 19 of 2013 (Need help getting this date with SQL)
  6. Saturday of week 19  of 2013 (Need help getting this date with SQL)

Sample Data

  1. 5/6/2014
  2. 19
  3. 5/4/2014
  4. 5/10/2014
  5. 5/5/2013
  6. 5/11/2013

SQL Queseries

  1. NA
  2. SELECT datepart(week, '05/06/2014')
  3. SELECT  CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, '05/06/2014') - 6, '05/06/2014')), 101)
  4. SELECT CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, '05/06/2014'), '05/06/2014')), 101)
  5. Man… I m hitting sql block here
  6. Again...hitting sql block here
Andriy M
  • 76,112
  • 17
  • 94
  • 154
user3311497
  • 11
  • 1
  • what version of Sql server are you running? – gh9 Feb 14 '14 at 19:31
  • `SELECT extract(week FROM now());` Although I don't really understood what do you want, this might help – Alexander Feb 14 '14 at 19:31
  • 2
    This question appears to be off-topic because it is unclear what you know – Kermit Feb 14 '14 at 19:33
  • DATEPART function can return the week number but that will only get you this far. You will have to resort to some TSQL-fu I can't think of right off my head. :) – Crono Feb 14 '14 at 19:35

1 Answers1

0

Is that what you need??

SELECT CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw,  DATEADD(year, -1, '05/06/2014')) - 6, DATEADD(year, -1, '05/06/2014'))), 101)
SELECT CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw,  DATEADD(year, -1, '05/06/2014')), DATEADD(year, -1, '05/06/2014'))), 101)

Also, I would use some variables if this is your case.

declare @date Datetime = '05/06/2014'
declare @datePreviousYear Datetime = DATEADD(year, -1, @date)
declare @currentYeardateCalculated int = @@DATEFIRST - DATEPART(dw,  @date)
declare @lastYearDateCalculated int = @@DATEFIRST - DATEPART(dw,  @datePreviousYear)

--1
SELECT @date
--2
SELECT datepart(week,@date )
--3
SELECT  CONVERT(varchar(50), (DATEADD(dd, @currentYeardateCalculated - 6, @date)), 101)
--4
SELECT CONVERT(varchar(50), (DATEADD(dd, @currentYeardateCalculated, @date)), 101)

--5
SELECT  CONVERT(varchar(50), (DATEADD(dd, @lastYearDateCalculated - 6,@datePreviousYear)), 101)
--6
SELECT CONVERT(varchar(50), (DATEADD(dd, @lastYearDateCalculated , @datePreviousYear)), 101)
Yumei De Armas
  • 386
  • 2
  • 6