SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Adding a Month Datetime
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jim_jim
Constraint Violating Yak Guru

USA
301 Posts

Posted - 11/13/2012 :  11:57:24  Show Profile  Reply with Quote
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

5155 Posts

Posted - 11/13/2012 :  12:22:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/13/2012 :  12:42:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/13/2012 :  12:44:33  Show Profile  Reply with Quote
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

USA
301 Posts

Posted - 11/13/2012 :  12:57:39  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000