sql server - SQL - Convert String Values to a Date -

here question trying solve:

find same day of same week of previous month 2014/12/01. result should 2014/11/03.

my approach taking values of original input date 2014/12/01 , breaking them down component parts (month-(1), day name, day value, week of month, year) below query:

i've put sqlfiddle query determine of output values:

declare @date datetime set @date = '2014-12-01'  select    (month(@date)-1) 'month',   datename(dw, @date) 'day name',   datepart(dw, @date) 'day value',   (datepart(week, @date) - datepart(week, dateadd(mm, datediff(mm,0,@date), 0))+ 1) 'week of month',   year(@date) 'year'; 

the output query below:

----------------------------------------------------- month | day name | day value |  week of month | year | ----------------------------------------------------- 11    | monday   | 2         |  1             | 2014 | ----------------------------------------------------- 

what i'd next take component parts , turn them date time value can a solution output of '2014/11/03'.

i'm struggling part of request can't find built-in function turn day name , day of week given month actual datetime output.

help please! in advance feedback.

query

declare @date datetime set @date = '2014-12-01'  ;with dates   (   select dateadd(day, -40 ,@date) datedata   union   select dateadd(day , 1 ,datedata)    dates    datedata < @date ) select *  dates month(datedata) = month(@date) - 1  ,  floor((day(datedata)-1)/7)+1 = floor((day(@date)-1)/7)+1  ,  datepart(weekday,datedata) = datepart(weekday,@date) 

result:

2014-11-03 00:00:00.000