Solved

SQL Date Problem

Posted on 2010-09-03
4
309 Views
Last Modified: 2012-06-27
Hello experts,
I have found a great code sample to create a date table. But when I
 try to appy the following select statement then I get 01/09/2010 as thursday.

My select statement is:
set dateformat MDY
select * from dim_date
where DATEPART(MONTH,[date])='09' and DATEPART(YEAR,[date])='2010'

But it's  wednesday.
How can I fix this SQL code..Please help
--Create the tables

BEGIN TRY

 DROP TABLE [dim_Date]

END TRY

BEGIN CATCH

 --DO NOTHING

END CATCH

CREATE TABLE [dbo].[dim_Date](

 --[ID] [int] IDENTITY(1,1) NOT NULL--Use this line if you just want an autoincrementing counter AND COMMENT BELOW LINE

 [ID] [int] NOT NULL--TO MAKE THE ID THE YYYYMMDD FORMAT USE THIS LINE AND COMMENT ABOVE LINE.

 , [Date] [datetime] NOT NULL

 , [Day] [char](2) NOT NULL

 , [DaySuffix] [varchar](4) NOT NULL

 , [DayOfWeek] [varchar](9) NOT NULL

 , [DOWInMonth] [TINYINT] NOT NULL

 , [DayOfYear] [int] NOT NULL

 , [WeekOfYear] [tinyint] NOT NULL

 , [WeekOfMonth] [tinyint] NOT NULL

 , [Month] [char](2) NOT NULL

 , [MonthName] [varchar](9) NOT NULL

 , [Quarter] [tinyint] NOT NULL

 , [QuarterName] [varchar](6) NOT NULL

 , [Year] [char](4) NOT NULL

 , [StandardDate] [varchar](10) NULL

 , [HolidayText] [varchar](50) NULL

 CONSTRAINT [PK_dim_Date] PRIMARY KEY CLUSTERED 

 (

 [ID] ASC

 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

 ) ON [PRIMARY]



GO

SET ANSI_PADDING OFF

BEGIN TRY

 DROP TABLE [dim_Time]

END TRY

BEGIN CATCH

 --DO NOTHING

END CATCH

CREATE TABLE [dbo].[dim_Time](

 [ID] [int] IDENTITY(1,1) NOT NULL,

 [Time] [char](8) NOT NULL,

 [Hour] [char](2) NOT NULL,

 [MilitaryHour] [char](2) NOT NULL,

 [Minute] [char](2) NOT NULL,

 [Second] [char](2) NOT NULL,

 [AmPm] [char](2) NOT NULL,

 [StandardTime] [char](11) NULL,

 CONSTRAINT [PK_dim_Time] PRIMARY KEY CLUSTERED 

 (

 [ID] ASC

 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

 ) ON [PRIMARY]



GO

SET ANSI_PADDING OFF



--Populate Date dimension

PRINT convert(varchar,getdate(),113) --To see the exact run time.

TRUNCATE TABLE dim_Date



--IF YOU ARE USING THE YYYYMMDD format for the primary key then you need to comment out this line.

--DBCC CHECKIDENT (dim_Date, RESEED, 60000) --In case you need to add earlier dates later.



DECLARE @tmpDOW TABLE (DOW INT, Cntr INT)--Table for counting DOW occurance in a month

INSERT INTO @tmpDOW(DOW, Cntr) VALUES(1,0)--Used in the loop below

INSERT INTO @tmpDOW(DOW, Cntr) VALUES(2,0)

INSERT INTO @tmpDOW(DOW, Cntr) VALUES(3,0)

INSERT INTO @tmpDOW(DOW, Cntr) VALUES(4,0)

INSERT INTO @tmpDOW(DOW, Cntr) VALUES(5,0)

INSERT INTO @tmpDOW(DOW, Cntr) VALUES(6,0)

INSERT INTO @tmpDOW(DOW, Cntr) VALUES(7,0)



DECLARE @StartDate datetime

 , @EndDate datetime

 , @Date datetime

 , @WDofMonth INT

 , @CurrentMonth INT

 

SELECT @StartDate = '1/1/2007'

 , @EndDate = '1/1/2015'--Non inclusive. Stops on the day before this.

 , @CurrentMonth = 1 --Counter used in loop below.



SELECT @Date = @StartDate



WHILE @Date < @EndDate

 BEGIN

 

 IF DATEPART(MONTH,@Date) <> @CurrentMonth 

 BEGIN

 SELECT @CurrentMonth = DATEPART(MONTH,@Date)

 UPDATE @tmpDOW SET Cntr = 0

 END



 UPDATE @tmpDOW

 SET Cntr = Cntr + 1

 WHERE DOW = DATEPART(DW,@DATE)



 SELECT @WDofMonth = Cntr

 FROM @tmpDOW

 WHERE DOW = DATEPART(DW,@DATE) 



 INSERT INTO dim_Date

 (

 [ID],--TO MAKE THE ID THE YYYYMMDD FORMAT UNCOMMENT THIS LINE... Comment for autoincrementing.

 [Date]

 , [Day]

 , [DaySuffix]

 , [DayOfWeek]

 , [DOWInMonth]

 , [DayOfYear]

 , [WeekOfYear]

 , [WeekOfMonth] 

 , [Month]

 , [MonthName]

 , [Quarter]

 , [QuarterName]

 , [Year]

 )

 SELECT CONVERT(VARCHAR,@Date,112), --TO MAKE THE ID THE YYYYMMDD FORMAT UNCOMMENT THIS LINE COMMENT FOR AUTOINCREMENT

 @Date [Date]

 , DATEPART(DAY,@DATE) [Day]

 , CASE 

 WHEN DATEPART(DAY,@DATE) IN (11,12,13) THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'th'

 WHEN RIGHT(DATEPART(DAY,@DATE),1) = 1 THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'st'

 WHEN RIGHT(DATEPART(DAY,@DATE),1) = 2 THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'nd'

 WHEN RIGHT(DATEPART(DAY,@DATE),1) = 3 THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'rd'

 ELSE CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'th' 

 END AS [DaySuffix]

 , CASE DATEPART(DW, @DATE)

 WHEN 1 THEN 'Monday'

 WHEN 2 THEN 'Tuesday'

 WHEN 3 THEN 'Wednesday'

 WHEN 4 THEN 'Thursday'

 WHEN 5 THEN 'Friday'

 WHEN 6 THEN 'Saturday'

 WHEN 7 THEN 'Sunday'

 END AS [DayOfWeek]

 , @WDofMonth [DOWInMonth]--Occurance of this day in this month. If Third Monday then 3 and DOW would be Monday.

 , DATEPART(dy,@Date) [DayOfYear]--Day of the year. 0 - 365/366

 , DATEPART(ww,@Date) [WeekOfYear]--0-52/53

 , DATEPART(ww,@Date) + 1 -

 DATEPART(ww,CAST(DATEPART(mm,@Date) AS VARCHAR) + '/1/' + CAST(DATEPART(yy,@Date) AS VARCHAR)) [WeekOfMonth]

 , DATEPART(MONTH,@DATE) [Month]--To be converted with leading zero later. 

 , DATENAME(MONTH,@DATE) [MonthName]

 , DATEPART(qq,@DATE) [Quarter]--Calendar quarter

 , CASE DATEPART(qq,@DATE) 

 WHEN 1 THEN 'First'

 WHEN 2 THEN 'Second'

 WHEN 3 THEN 'Third'

 WHEN 4 THEN 'Fourth'

 END AS [QuarterName]

 , DATEPART(YEAR,@Date) [Year]



 SELECT @Date = DATEADD(dd,1,@Date)

 END



--You can replace this code by editing the insert using my functions dbo.DBA_fnAddLeadingZeros

UPDATE dbo.dim_Date

 SET [DAY] = '0' + [DAY]

 WHERE LEN([DAY]) = 1



UPDATE dbo.dim_Date

 SET [MONTH] = '0' + [MONTH]

 WHERE LEN([MONTH]) = 1



UPDATE dbo.dim_Date

 SET STANDARDDATE = [MONTH] + '/' + [DAY] + '/' + [YEAR]



--Add HOLIDAYS --------------------------------------------------------------------------------------------------------------

--THANKSGIVING --------------------------------------------------------------------------------------------------------------

--Fourth THURSDAY in November.

UPDATE DIM_DATE

SET HolidayText = 'Thanksgiving Day'

WHERE [MONTH] = 11 

 AND [DAYOFWEEK] = 'Thursday' 

 AND [DOWInMonth] = 4

GO



--CHRISTMAS -------------------------------------------------------------------------------------------

UPDATE dbo.dim_Date

SET HolidayText = 'Christmas Day'

WHERE [MONTH] = 12 AND [DAY] = 25



--4th of July ---------------------------------------------------------------------------------------------

UPDATE dbo.dim_Date

SET HolidayText = 'Independance Day'

WHERE [MONTH] = 7 AND [DAY] = 4



-- New Years Day ---------------------------------------------------------------------------------------------

UPDATE dbo.dim_Date

SET HolidayText = 'New Year''s Day'

WHERE [MONTH] = 1 AND [DAY] = 1



--Memorial Day ----------------------------------------------------------------------------------------

--Last Monday in May

UPDATE dbo.dim_Date

SET HolidayText = 'Memorial Day'

FROM dim_Date

WHERE ID IN 

 (

 SELECT MAX([ID])

 FROM dbo.dim_Date

 WHERE [MonthName] = 'May'

 AND [DayOfWeek] = 'Monday'

 GROUP BY [YEAR], [MONTH]

 )

--Labor Day -------------------------------------------------------------------------------------------

--First Monday in September

UPDATE dbo.dim_Date

SET HolidayText = 'Labor Day'

FROM dim_Date

WHERE ID IN 

 (

 SELECT MIN([ID])

 FROM dbo.dim_Date

 WHERE [MonthName] = 'September'

 AND [DayOfWeek] = 'Monday'

 GROUP BY [YEAR], [MONTH]

 )



-- Valentine's Day ---------------------------------------------------------------------------------------------

UPDATE dbo.dim_Date

SET HolidayText = 'Valentine''s Day'

WHERE [MONTH] = 2 AND [DAY] = 14



-- Saint Patrick's Day -----------------------------------------------------------------------------------------

UPDATE dbo.dim_Date

SET HolidayText = 'Saint Patrick''s Day'

WHERE [MONTH] = 3 AND [DAY] = 17

GO

--Martin Luthor King Day ---------------------------------------------------------------------------------------

--Third Monday in January starting in 1983

UPDATE dim_Date

SET HolidayText = 'Martin Luthor King Jr Day'

WHERE [MONTH] = 1--January

 AND [Dayofweek] = 'Monday'

 AND [YEAR] >= 1983--When holiday was official

 AND [DOWInMonth] = 3--Third X day of current month.

GO

--President's Day ---------------------------------------------------------------------------------------

--Third Monday in February.

UPDATE dim_Date

SET HolidayText = 'President''s Day'--select * from dim_date

WHERE [MONTH] = 2--February

 AND [Dayofweek] = 'Monday'

 AND [DOWInMonth] = 3--Third occurance of a monday in this month.

GO

--Mother's Day ---------------------------------------------------------------------------------------

--Second Sunday of May

UPDATE dim_Date

SET HolidayText = 'Mother''s Day'--select * from dim_date

WHERE [MONTH] = 5--May

 AND [Dayofweek] = 'Sunday'

 AND [DOWInMonth] = 2--Second occurance of a monday in this month.

GO

--Father's Day ---------------------------------------------------------------------------------------

--Third Sunday of June

UPDATE dim_Date

SET HolidayText = 'Father''s Day'--select * from dim_date

WHERE [MONTH] = 6--June

 AND [Dayofweek] = 'Sunday'

 AND [DOWInMonth] = 3--Third occurance of a monday in this month.

GO

--Halloween 10/31 ----------------------------------------------------------------------------------

UPDATE dbo.dim_Date

SET HolidayText = 'Halloween'

WHERE [MONTH] = 10 AND [DAY] = 31

--Election Day--------------------------------------------------------------------------------------

--The first Tuesday after the first Monday in November.

BEGIN TRY

 drop table #tmpHoliday

END TRY 

BEGIN CATCH

 --do nothing

END CATCH



CREATE TABLE #tmpHoliday(ID INT IDENTITY(1,1), DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))



INSERT INTO #tmpHoliday(DateID, [YEAR],[DAY])

 SELECT [id], [YEAR], [DAY]

 FROM dbo.dim_Date

 WHERE [MONTH] = 11

 AND [Dayofweek] = 'Monday'

 ORDER BY YEAR, DAY



DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @CURRENTYEAR INT, @MINDAY INT



SELECT @CURRENTYEAR = MIN([YEAR])

 , @STARTYEAR = MIN([YEAR])

 , @ENDYEAR = MAX([YEAR])

FROM #tmpHoliday



WHILE @CURRENTYEAR <= @ENDYEAR

 BEGIN

 SELECT @CNTR = COUNT([YEAR])

 FROM #tmpHoliday

 WHERE [YEAR] = @CURRENTYEAR



 SET @POS = 1



 WHILE @POS <= @CNTR

 BEGIN

 SELECT @MINDAY = MIN(DAY)

 FROM #tmpHoliday

 WHERE [YEAR] = @CURRENTYEAR

 AND [WEEK] IS NULL



 UPDATE #tmpHoliday

 SET [WEEK] = @POS

 WHERE [YEAR] = @CURRENTYEAR

 AND [DAY] = @MINDAY



 SELECT @POS = @POS + 1

 END



 SELECT @CURRENTYEAR = @CURRENTYEAR + 1

 END



UPDATE DT

SET HolidayText = 'Election Day'

FROM dbo.dim_Date DT

JOIN #tmpHoliday HL

 ON (HL.DateID + 1) = DT.ID

WHERE [WEEK] = 1



DROP TABLE #tmpHoliday

GO

--------------------------------------------------------------------------------------------------------

PRINT CONVERT(VARCHAR,GETDATE(),113)--USED FOR CHECKING RUN TIME.



--Load time data for every second of a day

DECLARE @Time DATETIME



SET @Time = CONVERT(VARCHAR,'12:00:00 AM',108)



TRUNCATE TABLE dim_Time



WHILE @Time <= '11:59:59 PM'

 BEGIN

 INSERT INTO dbo.dim_Time([Time], [Hour], [MilitaryHour], [Minute], [Second], [AmPm])

 SELECT CONVERT(VARCHAR,@Time,108) [Time]

 , CASE 

 WHEN DATEPART(HOUR,@Time) > 12 THEN DATEPART(HOUR,@Time) - 12

 ELSE DATEPART(HOUR,@Time) 

 END AS [Hour]

 , CAST(SUBSTRING(CONVERT(VARCHAR,@Time,108),1,2) AS INT) [MilitaryHour]

 , DATEPART(MINUTE,@Time) [Minute]

 , DATEPART(SECOND,@Time) [Second]

 , CASE 

 WHEN DATEPART(HOUR,@Time) >= 12 THEN 'PM'

 ELSE 'AM'

 END AS [AmPm]



 SELECT @Time = DATEADD(second,1,@Time)

 END



UPDATE dim_Time

SET [HOUR] = '0' + [HOUR]

WHERE LEN([HOUR]) = 1



UPDATE dim_Time

SET [MINUTE] = '0' + [MINUTE]

WHERE LEN([MINUTE]) = 1



UPDATE dim_Time

SET [SECOND] = '0' + [SECOND]

WHERE LEN([SECOND]) = 1



UPDATE dim_Time

SET [MilitaryHour] = '0' + [MilitaryHour]

WHERE LEN([MilitaryHour]) = 1



UPDATE dim_Time

SET StandardTime = [Hour] + ':' + [Minute] + ':' + [Second] + ' ' + AmPm

WHERE StandardTime is null

AND HOUR <> '00'



UPDATE dim_Time

SET StandardTime = '12' + ':' + [Minute] + ':' + [Second] + ' ' + AmPm

WHERE [HOUR] = '00'



--dim_date indexes---------------------------------------------------------------------------------------------

CREATE UNIQUE NONCLUSTERED INDEX [IDX_dim_Date_Date] ON [dbo].[dim_Date] 

(

[Date] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IDX_dim_Date_Day] ON [dbo].[dim_Date] 

(

[Day] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IDX_dim_Date_DayOfWeek] ON [dbo].[dim_Date] 

(

[DayOfWeek] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IDX_dim_Date_DOWInMonth] ON [dbo].[dim_Date] 

(

[DOWInMonth] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IDX_dim_Date_DayOfYear] ON [dbo].[dim_Date] 

(

[DayOfYear] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IDX_dim_Date_WeekOfYear] ON [dbo].[dim_Date] 

(

[WeekOfYear] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IDX_dim_Date_WeekOfMonth] ON [dbo].[dim_Date] 

(

[WeekOfMonth] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IDX_dim_Date_Month] ON [dbo].[dim_Date] 

(

[Month] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IDX_dim_Date_MonthName] ON [dbo].[dim_Date] 

(

[MonthName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IDX_dim_Date_Quarter] ON [dbo].[dim_Date] 

(

[Quarter] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IDX_dim_Date_QuarterName] ON [dbo].[dim_Date] 

(

[QuarterName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IDX_dim_Date_Year] ON [dbo].[dim_Date] 

(

[Year] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IDX_dim_Time_HolidayText] ON [dbo].[dim_Date] 

(

[HolidayText] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



--dim_Time indexes

CREATE UNIQUE NONCLUSTERED INDEX [IDX_dim_Time_Time] ON [dbo].[dim_Time] 

(

[Time] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IDX_dim_Time_Hour] ON [dbo].[dim_Time] 

(

[Hour] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IDX_dim_Time_MilitaryHour] ON [dbo].[dim_Time] 

(

[MilitaryHour] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IDX_dim_Time_Minute] ON [dbo].[dim_Time] 

(

[Minute] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IDX_dim_Time_Second] ON [dbo].[dim_Time] 

(

[Second] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IDX_dim_Time_AmPm] ON [dbo].[dim_Time] 

(

[AmPm] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IDX_dim_Time_StandardTime] ON [dbo].[dim_Time] 

(

[StandardTime] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



PRINT convert(varchar,getdate(),113)--USED FOR CHECKING RUN TIME.

Open in new window

0
Comment
Question by:innocent1973
  • 2
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33595969
select * from dim_date
where [Date] >= convert(datetime, '2010-09-01', 120)
  and [Date] < convert(datetime, '2010-10-01', 120)
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33595980
Before you run that script, you need a

SET DATEFIRST 1
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33595998
To elaborate, add SET DATEFIRST 1 to the top of the script and it will work correctly.
There is no point optimizing it since it finishes in seconds and is a one-off creation.  Pretty nice code.
0
 

Author Closing Comment

by:innocent1973
ID: 33596441
THANK YOU VERY MUCH...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now