DECLARE @SourceDate DateTime = '2012-12-16', @DimDate DateTime = '2012-12-15' DECLARE @RowCount int BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here WHILE CAST(@DimDate as Date) < CAST(@SourceDate as DATE) BEGIN set @DimDate = DATEADD(DAY,1,@DimDate ); SET @RowCount = 0 WHILE @RowCount <24 BEGIN select distinct DATEADD([hour], DATEDIFF([hour], 0, @DimDate), 0) ,CAST(@DimDate as Date) as [FullDateAlternateKey] ,datepart(weekday,@DimDate) as [DayNumberOfWeek] ,datename(weekday,@DimDate) as [DayNameOfWeek] ,datepart(day,@DimDate) as [DayNumberOfMonth] ,datepart(dayofyear,@DimDate) as [DayNumberOfYear] ,ltrim(str(datepart(week,@DimDate))) as [CalendarNumOfYear] ,'W'+ltrim(str(datepart(week,@DimDate))) as [WeekNumberOfYear] ,datename(month,@DimDate) as [MonthName] ,datepart(month,@DimDate) as [MonthNumberOfYear] ,'Q' + Ltrim(str(datepart(quarter,@DimDate))) as [CalendarQuarter] ,'CY ' + Ltrim(str(datepart(year,@DimDate))) as [CalendarYear] ,case when datepart(quarter,@DimDate) < 3 then 'H1' else 'H2' end as [CalendarSemester] --,'Q' + Ltrim(str(B.FiscalQuarterNumber)) as [FiscalQuarter] ,'FY ' + CAST((CASE WHEN DATEPART(MM, @DimDate) > 6 THEN DATEPART(YY, @DimDate) + 1 WHEN DATEPART(MM, @DimDate) <=6 THEN DATEPART(YY, @DimDate) END ) as varchar(7)) as FiscalYear ,Convert(nvarchar,@DimDate,101) as [strDate] ,datename(month,@DimDate) as FiscalMonthName SET @RowCount= @RowCount +1; END; END ENDFrom the above SQL stmt, everything working as I wanted to, but I'm looking to add a field which will have date and time as well; like : '2015-03-02 00:00:00.000',......., '2015-03-02 08:00:00.000', '2015-03-02 09:00:00.000',....'2015-03-02 23:00:00.000' each day.
Datetime from Date
April 27th, 2015 5:58pm
your question is unclear.. do you want to get the date column as datetime column or do you want columns with the above mentioned hours.
if you want column as datetime, instead of date. remove the CAST(@DimDate as date) ..just use @DIMDate
if you columns the hourly frequency you mentioned.. use below..replace getdate() with @DimDate
declare @a date select @a=getdate() select cast(@a as datetime),dateadd(hour,8,cast( @a as datetime)),dateadd(hour,9,cast( @a as datetime)),dateadd(hour,23,cast( @a as datetime))
Free Windows Admin Tool Kit Click here and download it now
April 27th, 2015 6:22pm
Would a simple DATEADD([hour], @rowcount,@DimDate) solve your issue?
April 28th, 2015 2:39am