sp to create date table

Question

sp to create date table

gissah

SSCommitted

Points: 1815
More actions
April 24, 2012 at 9:32 am

#267854

Hi all I have a sp that runs every morning to insert into a table dates. whenever I run the sp all I get is 1900 and wierd date. I have attach my sp can any one help tell me what I am doing wrong.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 1

I ran it by chopping off the insert portion as I didn't have a table definition and the code seemed to run ok, returning a bunch of rows with date info in columns. If you can post your table definition that the sp is inserting into then folks might be able to run your sp without modification and might be able to spot a problem.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

First, your procedure can be rewritten to work without using a loop.

Second, how are you executing the procedure?

gissah SSCommitted Points: 1815 More actions · Answer 3

CREATE TABLE [dbo].[tblDate](

[sDateID] [bigint] NOT NULL,

[FullDate] [datetime] NULL,

[DateName] [Date]NULL,

[DateNameUS] [Date] NULL,

[DateNameEU] [Date] NULL,

[DayOfWeek] [tinyint] NULL,

[DayNameOfWeek] [char](10) NULL,

[DayOfMonth] [tinyint] NULL,

[DayOfYear] [smallint] NULL,

[WeekdayWeekend] [char](7) NULL,

[WeekOfYear] [tinyint] NULL,

[MonthName] [char](10) NULL,

[MonthOfYear] [tinyint] NULL,

[IsLastDayOfMonth] [char](1) NULL,

[CalendarQuarter] [tinyint] NULL,

[CalendarYear] [smallint] NULL,

[CalendarYearMonth] [char](7) NULL,

[CalendarYearQtr] [char](7) NULL,

[FiscalMonthOfYear] [tinyint] NULL,

[FiscalQuarter] [tinyint] NULL,

[FiscalYear] [int] NULL,

[FiscalYearMonth] [char](9) NULL,

[FiscalYearQtr] [char](8) NULL,

[AuditKey] [bigint] IDENTITY(1,1) NOT NULL,

[WeekBeginDate] [datetime] NULL,

[WeekEndDate] [datetime] NULL,

[IsLastDayOfWeek] [char](1) NULL,

CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED

CapnHector SSCoach Points: 16143 More actions · Answer 4

looks like you are creating your calendar table every morning?? is that correct?? i have a calendar table which just sits there and i have it out to 2016-01-01 right now. same type of column setup.

EDIT: Also your script has some errors in it either your DDL or your SP. most likely your DDL right now since you can run the script but it gives you weird dates

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

gissah SSCommitted Points: 1815 More actions · Answer 5

gissah

SSCommitted

Points: 1815

April 24, 2012 at 12:57 pm

#1478476

yes and yes.

can you share your sample code.

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 6

Is there a reason why you create the calendar table daily?

Calendar tables are usually fairly static, with occasional added columns for various indicators. My calendar table was created almost 5 years ago and runs out 100 years. I think I've added 1 column in that time.

I could see a yearly update with company holiday information or internaionalization updates, but daily?

Just curious.

Converting oxygen into carbon dioxide, since 1955.

gissah SSCommitted Points: 1815 More actions · Answer 7

maybe that is the best way to go, yearly. can you please guide me through it.

gissah SSCommitted Points: 1815 More actions · Answer 8

i found a help here Thanks

http://www.sqlservercentral.com/scripts/Date/68389/

CapnHector SSCoach Points: 16143 More actions · Answer 9

gissah (4/24/2012)
maybe that is the best way to go, yearly. can you please guide me through it.

here is part of my calendar table script. the tally in the from is my tally table which goes to 11000. there are different methods to generate the basic dates and in the link you posted they use a recursive CTE which in this case is not needed. you can also use a FROM sys.columns a, sys.columns b instead of a tally table. for my date parts (year day number week number quarter number) i use calculated columns so i only have to worry about setting the workdays. for the end of x i have queries written to update those columns to set them to 1. but since calendar tables are static (dates dont change on a daily basis) once created in only update and add a column if a new process cant use one of the columns i all ready have.

CREATE TABLE Calendar (

CalDate DATETIME PRIMARY KEY CLUSTERED,

Holiday BIT DEFAULT 0,

Workday BIT DEFAULT 0,

DayNum AS DATEPART(DAYOFYEAR,CalDate),

WeekNum AS DATEPART(WEEK,CalDate),

QuarterNum AS DATEPART(Q,caldate),

EndOfWeek BIT DEFAULT 0,

EndOfMonth BIT DEFAULT 0,

EndOfQuarter BIT DEFAULT 0,

)

INSERT Calendar (CalDate) -- insert dates into calendar

SELECT '2011-01-01'

UNION ALL

SELECT TOP (DATEDIFF(dd,'2011-01-01','2016-01-01')) DATEADD (dd,t.N,'2011-01-01') FROM Tally t

UPDATE Calendar -- create weekends and workdays

SET Holiday = 1 WHERE DATENAME(dw,CalDate) in ('Saturday','Sunday')

-- then set the rest of your holidays

UPDATE Calendar -- new years

SET Holiday = 1

WHERE DATEPART (mm,caldate) = 1 and DATEPART (dd,caldate) = 1

UPDATE Calendar -- create workdays

SET Workday = 1 WHERE Holiday = 0

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

gissah SSCommitted Points: 1815 More actions · Answer 10

Thanks will look into it. The first one seems good for me