Author |
Topic |
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2012-11-13 : 11:57:24
|
Hi Allwe are creating a new process where in we take the original request received date and 'n' create number of duplicate requests.n is derived by subtracting two date feildsin this process each duplicate request we create is for subsequent months.So for example if we receive a request in Jan 2013 we will create 11 duplicate requests until end of year 2013so i need to insert received date for the duplicate request as a constant date in every month for all the duplicate monthsif the original request received date is 1/14/2013i need to insert for the next 11 duplicate requests should be2/20/20133/20/20134/20/20135/20/20136/20/20137/20/20138/20/20139/20/201310/20/201311/20/20132/20/201312/20/2013I have been using the below.It is incrementing months appropriately but it is taking the original request receiveddate and adding to a month to it.I would like to change the below so that it is able to insert a date with a date that i choose instead of the original receiveddateconvert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate)) |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-13 : 12:22:29
|
See below, but1. If your @day is 29 or more, when it gets to February, it can fail.2. If you need to generate a series of dates, instead of using a variable (@count), join to a numbers tables.3. Unless there is a compelling reason, keep the dates as datetime type rather than converting them to VARCHAR(30)DECLARE @dt DATETIME = '20130114'; -- original received dateDECLARE @day INT = 20; -- date you want in the duplicatesDECLARE @count INT = 5; -- months to addSELECT DATEADD(mm,DATEDIFF(mm,0,@dt)+@count,@day-1); |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-13 : 12:42:14
|
A bit wordy, but no loops SELECT DATEADD(mm,DATEDIFF(mm,0,@dt)+mon.num,@day-1)FROM( VALUES (0),(2),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11) ) mon(Num)WHERE DATEADD(mm,DATEDIFF(mm,0,@dt)+mon.num,@day-1) < DATEADD(year,DATEDIFF(year,-1,@dt),0)JimEveryday I learn something that somebody else already knew |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-13 : 12:44:33
|
Or, for people who think before hitting "Submit Reply"SELECT DATEADD(mm,DATEDIFF(mm,0,@dt)+mon.num,@day-1)FROM( VALUES (0),(2),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11) ) mon(Num)WHERE mon.num < DATEDIFF(MONTH,@dt,DATEADD(year,DATEDIFF(year,-1,@dt),0))JimEveryday I learn something that somebody else already knew |
|
|
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2012-11-13 : 12:57:39
|
Thank You guys.I will spend time understanding it and implementing it and come back if it does not work |
|
|
|
|
|