i want fill table dim_date(iddate,date,year,month,trimester,weeknumber,week,day) calendar , date format "year/month/day hh:mm can please me best regards
im making assumptions here since didnt format of date format in (i assumed varchar) , didnt specify weeknumber , week was...so assumed week of year , week of month:
-->set data example create table dummy_date_table (dummy_date varchar(50)) go -->insert dummy data(assuming have these somewhere text?) insert dummy_date_table (dummy_date) values ('2010/11/01 01:22'), ('2013/06/02 03:16'), ('2012/07/03 05:32'), ('2011/01/04 06:04') go -->create date dimension based on logic create table dim_date( iddate int, -->pk date datetime, -->full date year int,-->year of date month int,-->month of date trimester int,-->trimester 1,2,3 weeknumber int,-->week number in year week int, -->week number in month day int --.day part of date ) go -->used primary key|date|year|month|trimester|weeknumber|week|day declare @i int=1, @date datetime, @year int, @month int, @trimester int, @weeknumber int, @week int, @day int -->cursor insert our data declare date_inserter cursor select convert(datetime,left(dummy_date,10)), datepart(year,convert(datetime,left(dummy_date,10))), datepart(month,convert(datetime,left(dummy_date,10))), case when datepart(month,convert(datetime,left(dummy_date,10))) <5 1 when datepart(month,convert(datetime,left(dummy_date,10))) >4 , datepart(month,convert(datetime,left(dummy_date,10))) <9 2 else 3 end, datepart(iso_week,convert(datetime,left(dummy_date,10))), datediff(week, dateadd(month, datediff(month, 0, convert(datetime,left(dummy_date,10))), 0), convert(datetime,left(dummy_date,10))) +1 , datepart(day,convert(datetime,left(dummy_date,10))) dummy_date_table; open date_inserter; fetch next date_inserter @date, @year, @month, @trimester, @weeknumber, @week, @day while @@fetch_status = 0 begin insert dim_date select @i, @date, @year, @month, @trimester, @weeknumber, @week, @day set @i+=1; fetch next date_inserter @date, @year, @month, @trimester, @weeknumber, @week, @day end -->cleanup close date_inserter deallocate date_inserter go -->look @ data select * dim_date results:
iddate date year month trimester weeknumber week day 1 2010-11-01 00:00:00.000 2010 11 3 44 1 1 2 2013-06-02 00:00:00.000 2013 6 2 22 2 2 3 2012-07-03 00:00:00.000 2012 7 2 27 1 3 4 2011-01-04 00:00:00.000 2011 1 1 1 2 4 
Comments
Post a Comment