Script for Datetime - 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

General SQL Server Forums

New to SQL Server Programming

Script for Datetime

Author

Topic

rajan_kr
Starting Member

9 Posts

Posted - 2008-07-18 : 12:21:23

Hi All,

Please help me in this.
I have a table [dbo].[FISCAL_QTR]
([FISCAL_QTR_ID]
,[FISCAL_QTR]
,[FISCAL_YEAR]
,[DESCR]
,[START_DATE]
,[END_DATE])
and the data's in this table is like this
41,1,2010,2-2010,11/2/2009 12:00:00 AM,1/31/2010 12:00:00 AM
42,2,2010,2-2010,2/1/2010 12:00:00 AM,5/2/2010 12:00:00 AM
43,3,2010,3-2010,5/3/2010 12:00:00 AM,8/1/2010 12:00:00 AM
44,4,2010,4-2010,8/2/2010 12:00:00 AM,10/31/2010 12:00:00 AM

now i have to expand the table till 2015, with the same format.
Can anyone help me in writing a script for the same.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-18 : 12:32:26

Are you being lazy? Thats your job.

rajan_kr
Starting Member

9 Posts

Posted - 2008-07-18 : 12:34:06

i know but that y i am looking for a help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-18 : 12:58:38

quote:
Originally posted by sodeep

Are you being lazy? Thats your job.

Why are you being so rude?

rajan_kr, I don't understand your issue. Are you just trying to populate rows?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rajan_kr
Starting Member

9 Posts

Posted - 2008-07-18 : 13:05:10

thanks Tara for replying me and helping me out, ya i want to populate rows through the scripts.
basically i wanted to write a script through which i can insert data in the specified field in the specified format till 2015

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-18 : 13:07:09

quote:
Originally posted by tkizer

quote:
Originally posted by sodeep

Are you being lazy? Thats your job.

Why are you being so rude?
rajan_kr, I don't understand your issue. Are you just trying to populate rows?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Excuse me

I am not being rude over here. I am trying to help him learn himself.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-18 : 13:07:48

Could you elaborate on your format? For instance, how would we know what to put in the 4th, 5th, and 6th columns?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rajan_kr
Starting Member

9 Posts

Posted - 2008-07-18 : 13:11:53

let me brief once again
CREATE TABLE [dbo].[FISCAL_QTR](
[FISCAL_QTR_ID] [int] NOT NULL,
[FISCAL_QTR] [int] NOT NULL,
[FISCAL_YEAR] [int] NOT NULL,
[DESCR] [varchar](30) NOT NULL,
[START_DATE] [datetime] NOT NULL,
[END_DATE] [datetime] NOT NULL
) ON [PRIMARY]
this is my table
and the data's in this table is like this
41,1,2010,2-2010,11/2/2009 12:00:00 AM,1/31/2010 12:00:00 AM
42,2,2010,2-2010,2/1/2010 12:00:00 AM,5/2/2010 12:00:00 AM
43,3,2010,3-2010,5/3/2010 12:00:00 AM,8/1/2010 12:00:00 AM
44,4,2010,4-2010,8/2/2010 12:00:00 AM,10/31/2010 12:00:00 AM

so this is my script
CREATE CLUSTERED INDEX id ON dbo.FISCAL_QTR(FISCAL_QTR_ID)
GO

Declare @year int
Set @year = '2011'
While @year <= '2015'
Begin

Declare @id int
Declare @Qtr int
Declare @startDate datetime
Declare @enddate datetime

SET @id = (SELECT (Max(FISCAL_QTR_ID))
From dbo.FISCAL_QTR)
Set @startdate =(Select DATEADD(day,1,END_DATE)FROM dbo.FISCAL_QTR
where FISCAL_QTR_ID=@id)
Set @enddate =(Select DATEADD(Week,13,END_DATE)FROM dbo.FISCAL_QTR
where FISCAL_QTR_ID=@id)

INSERT INTO dbo.FISCAL_QTR(FISCAL_QTR_ID,DESCR,START_DATE,END_DATE)
VALUES ((@id+1),@year,@startdate,@enddate)

SET @year = @year + 1
END

drop index id on dbo.FISCAL_QTR

which is incomplete and i dont know how to proced now.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-18 : 13:17:26

I can't help you unless you explain how to derive 4th, 5th, and 6th columns. We can't code anything until we know more.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rajan_kr
Starting Member

9 Posts

Posted - 2008-07-18 : 13:26:28

okey let me elobrate again
my quarter format is 4-4-5 mean all total 13 weeks coorect
and my year starts when you add 52weeks into the enddate of the last quarter.
so the column 1st has the int value and incremental
column 2nd is the quarter column based on startdate and enddate so 1,2,3,4
3rd column is year is the year that is based on enddate of the last quarter.
4th is desc which is the quarter+year
5th is the start colum which is based in previous enddate
6th is the enddate for every quarter

rajan_kr
Starting Member

9 Posts

Posted - 2008-07-18 : 14:06:07

is any who gona help me

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-18 : 14:11:26

I would help, but I'm thoroughly confused.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rajan_kr
Starting Member

9 Posts

Posted - 2008-07-18 : 14:25:35

okey Tara let me breif one more time.

this is the quarter table.
so my quarter is 4-4-5 weeks thats is not based on calender year but my year.
my year is of 52weeks means enddate of forth quarter plus 52weeks.
so the script for the year is
(Select DATEADD(Week,52,END_DATE)FROM dbo.FISCAL_QTR
where FISCAL_QTR_ID=44)
this is give you the enddate for the year.
but i want this whole year should get divided into quarters and that should get entered in the fields
that means in a year there is 4 quartes. which is 13weeks each
so the data will be like this.
45, 1, 2011, 1-2011, 2010-11-01 00:00:00.000, 2011-01-30 00:00:00.000 ---note year is 2011 beac this is current fiscal year.
46, 2, 2011, 2-2011, 2011-01-31 00.00.00.000, 2011-05-01 00:00:00.000
47, 3, 2011, 3-2011, 2011-05-02 00.00.00.000, 2011-07-31 00:00:00.000
48, 4, 2011, 4-2011, 2011-08-01 00.00.00.000, 2011-10-30 00:00:00.000

same way for year 2012....
please help me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources