Datetime from Date
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
END
From 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.
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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics