How to extract week from date

[tushar gaurav]

How we can extract week from date i.e. if today is 25thJune2015 so how we can find this date belongs to which week of month??

[Lynn]

Look at the calendar on the wall, find the desired date, start counting at the first week and then stop when you get to the week containing the desired date.

All kidding aside, if you want do this in an automated fashion you will need to provide more details.

Do you have a relational package or dimensional? If relational, what DBMS? Does your definition of a week run from Monday to Sunday or from Sunday to Saturday or something else? How do you intend to handle partial weeks where there are dates in the week that span two months?

Assuming relational, you might be able to look at the example below as a starting point that you can refine further around your specific requirements.

The extract function gets the day number for the date in question. This number is divided by seven because we have seven days in a week. The floor function takes the integer portion only (part to the left of the decimal) from this calculation to which we then add 1 to get the week number.

Code Select Expand


floor ( extract ( day, [Calendar Date] ) / 7 ) + 1


[bdbits]

Again assuming relational, if you will be doing a lot of date calculations I highly recommend you build a time table with a lot of precalculated attributes for each date. Then use this table as a foreign key reference wherever you have a date. For example, for each day you might have the date, day of the week, day name, whether it is a work day or a holiday, and calculations for the calendar and fiscal week, month, quarter and year, etc. This becomes immensely useful for report authors and makes filtering on a day of the week incredibly simple.

[Lynn]

Again assuming relational, if you will be doing a lot of date calculations I highly recommend you build a time table with a lot of precalculated attributes for each date. Then use this table as a foreign key reference wherever you have a date. For example, for each day you might have the date, day of the week, day name, whether it is a work day or a holiday, and calculations for the calendar and fiscal week, month, quarter and year, etc. This becomes immensely useful for report authors and makes filtering on a day of the week incredibly simple.

So true!!

[rockytopmark]

Most Important Dimension in any Data Warehouse (IMNSHO):

Date

[cognostechie]

So true ! 

I recently worked for a company which follows a fiscal calendar different than the regular calendar. They made a slight change to their calendar this year but also wanted to see the results if they had kept the same calendar. I implemented a system that would let them run two calendars at the same time and every report can run for any of those calendars. Couldn't have been possible without creating some fields in the Date Dimension table. The prompt macro helped a lot in this too.

[bdbits]

I would agree that date/time is often the Most Important Dimension in any Data Warehouse.

I am currently working in US state government with a legislature that meets only every other year and has its own fiscal year (July 1). So, I have multiple time ranges for many subject areas - calendar year, state fiscal year, federal fiscal year, and biennium (the last is most often a top level in the other hierarchies). We have months, quarters, weeks, etc. precalculated for all of them. If modeling dimensionally, I most often use alternate drill paths in a single hierarchy that drills down to the day. It works out very well, actually.

[bdbits]

I started thinking about this so I checked one of our warehouses in the date dimension table. It has 57 columns. Seems like a lot to me.  ;D

[cognostechie]

Yes 57 is a lot. If it is absolutely required then it is ok otherwise it is better to clean it up because the more columns it has the slower it will become.

In some cases those columns might be required like marking marriage anniversary, girlfriend's birthday, entering old age etc  ;)

[bdbits]

They are actually required. We use a lot of conformed dimensions, so there are a lot of different needs across the subject areas.

is_bdbits_anniversary is not one of the columns, though.  :P