Bulk Insert of dates - 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 2005 Forums

Transact-SQL (2005)

Bulk Insert of dates

Author

Topic

BrettD
Starting Member

14 Posts

Posted - 2008-02-06 : 05:19:01

Hi All,

I'm after a quick and dirty method of inputting the following into my table.

Here's the table:

TABLE [dbo].[BookingDate](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Date] [smalldatetime] NOT NULL,
[Day] [varchar](10) NOT NULL,
[WeekNumber] [int] NOT NULL,
[Period] [int] NOT NULL,
[Quarter] [int] NOT NULL,
[Year] [varchar](10) NOT NULL,
CONSTRAINT [PK_BookingDate] PRIMARY KEY CLUSTERED
(
[ID] ASC
)

What I want is to write a record for each day since 29/01/2006 until 31/12/2015 containing the above fields.

eg.

Date Day WeekNumber Period Quarter Year
29/01/2006 Sunday 1 1 1 2006-2007
30/01/2006 Monday 1 1 1 2006-2007
...
05/02/2006 Sunday 2 1 1 2006-2007
06/02/2006 Monday 2 1 1 2006-2007
...
26/02/2006 Sunday 5 2 1 2006-2007
...
30/04/2006 Sunday 14 4 2 2006-2007

After 7 days, add one to the week, after 4 weeks add one to the period, after 3 periods add one to the quarter, after 4 quarters, start a new year with the weeks, periods and quarters starting from one and the year being 2007-2008

Is there a quick way to do this?

Thanks in advance,
Brett

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-06 : 05:31:40

I dont know if this is what you want.But I came up with this.You can modify it according your needs.

Declare @startdate datetime
Declare @enddate datetime
Set @startdate='01/29/2006'
Set @enddate='12/31/2015'

Select distinct
dateadd(dd,number,@startdate)as Date,
datename(weekday,dateadd(dd,number,@startdate)) as Weekday,
datename(week,dateadd(dd,number,@startdate))-4 as Week,
datename(quarter,dateadd(dd,number,@startdate)) as Quarter,
datename(year,dateadd(dd,number,@startdate))as Year
from master.dbo.spt_values where number between 0 and DATEDIFF(dd , @startdate , @enddate)

BrettD
Starting Member

14 Posts

Posted - 2008-02-06 : 06:47:35

Ayamas, thanks for that.

What I want is for 29/01/2006 to actually be Week 1 (to fit in with our date schema). If I do datename(week,dateadd(dd,number,@startdate))-4 as Week, the next year's weeks will start at -3.

Is there any way to force the startdate to count from Week 1 rather than me have to adjust it?

I will also have to cast datename(year,dateadd(dd,number,@startdate)) as a varchar because I need to output to be 2006-2007:

datename(year,dateadd(dd,number,@startdate)) + '-' + datename(year,dateadd(dd,number,@startdate))

Where do I do my casting?

Thanks again,
Brett

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources