Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Adding a Month Datetime

Author  Topic 

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-11-13 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-13 : 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);
Go to Top of Page

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)

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

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))


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -