| Author |
 Topic  |
|
|
jim_jim
Constraint Violating Yak Guru
USA
277 Posts |
 Posted - 11/13/2012 : 11:57:24
|
Hi All
we 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 feilds
in 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 2013
so i need to insert received date for the duplicate request as a constant date in every month for all the duplicate months
if the original request received date is 1/14/2013
i need to insert for the next 11 duplicate requests should be
2/20/2013
3/20/2013
4/20/2013
5/20/2013
6/20/2013
7/20/2013
8/20/2013
9/20/2013
10/20/2013
11/20/2013
2/20/2013
12/20/2013
I 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 receiveddate
convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate)) |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/13/2012 : 12:22:29
|
See below, but
1. 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 date
DECLARE @day INT = 20; -- date you want in the duplicates
DECLARE @count INT = 5; -- months to add
SELECT DATEADD(mm,DATEDIFF(mm,0,@dt)+@count,@day-1); |
 |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 11/13/2012 : 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)
Jim
Everyday I learn something that somebody else already knew |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 11/13/2012 : 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))
Jim
Everyday I learn something that somebody else already knew |
|
|
|
jim_jim
Constraint Violating Yak Guru
USA
277 Posts |
Posted - 11/13/2012 : 12:57:39
|
| Thank You guys.I will spend time understanding it and implementing it and come back if it does not work |
|
|
| |
Topic |
|
|
|
Adding a Month Datetime
