calculate date - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

calculate date

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2005-04-14 : 18:02:35

Hi All.
In my SQL Server I would like to create storage procedure to calculate date and append records to a Table1. I have fields:
Fields ------> Format -------------> Value
=========================================
ID ---------> (int 4, Identity = yes)
PackID ----> (varchar 4) ----------> 1, 2, 3, 4 then repeat again
DateFrom --> (varchar 10) --------> 2003/1/1
DateTo ----> (varchar 10) --------> 2005/3/30 (in this field the date will 15 and 28 or 30 or 31 dependably from a month)

The values of Table1:

ID    PackID      DateFrom      DateTo 
--------------------------------------------------- 
12        1       2003/1/1      2005/3/31 
13        2       2003/1/1      2005/3/31 
14        3       2003/1/1      2005/3/31 
15        4       2003/1/1      2005/3/31

after append records the values of Table1 should be

16        1       2003/1/1      2005/4/15 
17        2       2003/1/1      2005/4/15 
18        3       2003/1/1      2005/4/15 
19        4       2003/1/1      2005/4/15

next record that will append will be

20        1       2003/1/1      2005/4/30 
21        2       2003/1/1      2005/4/30 
22        3       2003/1/1      2005/4/30 
22        4       2003/1/1      2005/4/30

and so on

How to create storage procedure to repeat value for PackID and calculate date for DateTo and insert new record to Table1?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-14 : 18:12:32

First, they are called stored procedures not storage procedures. Second, you'll need to define your ID column to be an identity column so that SQL Server can manage the incrementing value. For DateTo, I'd assume you'd use GETDATE() function which you can put as a default on your column.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-04-15 : 01:08:35

[code]
CREATE PROCEDURE weirddatestuff

--SET DATEFORMAT YMD EXEC weirddatestuff '2001/04/01','2001/01/01',15

@startdate DATETIME,
@datefrom DATETIME,
@months INT

AS

SET DATEFORMAT YMD

SELECT @startdate = CAST(DATEPART(YY,@startdate) AS VARCHAR(4)) + '/' + CAST(DATEPART(MM,@startdate) AS VARCHAR(2)) + '/01'

--Create the table to hold all this crap.
CREATE TABLE #weirddatepopulation(
IhatecolumnscalledID INT IDENTITY(1,1),
PackID VARCHAR(4), --Why is this varchar(4)? It should be an INT.
DateFrom DATETIME,
DateTo DATETIME)

--Hold your PackID series.
DECLARE @packer TABLE(PackID INT) --Love these names.

INSERT @packer(PackID)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4

--Populate a date table with the appropriate dates.
DECLARE @datestuff TABLE(datestuffed DATETIME)

DECLARE
@min INT,
@startdatemid DATETIME,
@startdateend DATETIME,
@minplusone INT

SELECT @min = 0

WHILE @min < @months
BEGIN

SELECT @minplusone = @min + 1

SELECT
@startdatemid = DATEADD(DD,14,DATEADD(MM,@min,@startdate)),
@startdateend = DATEADD(DD,-1,DATEADD(MM,@minplusone,@startdate))

INSERT @datestuff(datestuffed)
SELECT @startdatemid UNION ALL
SELECT @startdateend

SELECT @min = @min + 1
END

INSERT #weirddatepopulation(
PackID,
DateFrom,
DateTo)

SELECT
p.PackID,
@datefrom,
ds.datestuffed
FROM
@datestuff ds
CROSS JOIN @packer p
ORDER BY
ds.datestuffed,
p.PackID

SELECT
IhatecolumnscalledID,
PackID,
DateFrom,
DateTo
FROM
#weirddatepopulation
GO

[/code]

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources