dBforums – Everything on Databases, Design, Developers and Administrators. Examples of how to Calculate Different SQL Server Dates. Every now and then, you need to take the current date and calculate some other date.
For instance, you might have an application that needs to determine what date is the first day of the month, or need to know the last day of the month. Now most of you probably already know how to separate the date into its piece (year, month, day, etc.) and use those pieces along with a number of functions to calculate a date that you might need. In this article, I will be showing how to use just the DATEADD and DATEDIFF function to calculate a number of different dates you might need to use in your applications. In order to understand these examples, let's first review the DATEDIFF and DATEADD functions. The DATEDIFF function calculates the amount of time between two dates, where the time part is based on an interval of time, such as hours, days, weeks, months, years, etc. First Day of Month For the first example, let me show you how to get the first day of the month from the current date. Conclusion. Calculating business days between dates in SQL (a datediff function without weekends)
Examples of how to Calculate Different SQL Server Dates. Date Calculation Excluding Weekend & Holiday. Here's a user defined function that does what you need.
You will have to create tblHolidays and populate it with all the holidays you want excluded. CREATE FUNCTION [dbo]. [ElapsedBDays] (@Start smalldatetime, @End smalldatetime)RETURNS int ASBEGIN/*Description: Function designed to calculate the number of business days (In hours)between two dates. */DECLARE @Days int ,@WeekDays int ,@Holidays int ,@Hours int SELECT @Hours = DATEDIFF(Hour,@Start,@End)WHILE (DATEPART(WeekDay,@Start)-1) % 6 = 0BEGIN SELECT @Start = DATEADD(Day,1,@Start) SELECT @Hours = @Hours - 24ENDWHILE (DATEPART(WeekDay,@End)-1) % 6 = 0BEGIN SELECT @End = DATEADD(Day,1,@End) SELECT @Hours = @Hours - 24END.
Returning a week number for any given date and starting fiscal month. By Paul Alcon on 2 May 2007 | 8 Comments | Tags: Queries Paul writes "Sql Server comes with a host of built in functions such as ISNULL, CONVERT and CAST.
Now if that wasn't enough rope to hang ourselves with, as of Sql Server 2000 we gained the ability to create our own user defined functions. In this article I will be looking at the three main date functions DATEADD, DATEPART and DATEDIFF (there is a fourth called DATENAME but I want to get to the end of this article before you fall asleep so I decided to leave it for another date and time! And no it doesn't foretell the name of your future blind date so it's not as interesting as it sounds anyway) Then I will be combining all three in a user defined function of our own by which time our necks will be well and truly stretched! " Sql Server comes with a host of built in functions such as ISNULL, CONVERT and CAST.
Populating U.S. Federal Holidays in a Calendar Table. The title of this blog is only half as thrilling as the SQL.
I can assure you. I have a calendar table at work that is based off of the calendar table that you can find on aspfaq.com in an article titled Why should I consider using an auxiliary calendar table? Create Date Dimension Table in SQL Server. Monday, September 2nd, 2013 at 3:10 pm | How to, SQL A date dimension table is essentially a big reference table that contains all dates that you could possibly use in your queries (generally starting from 1900-01-01) and going as far out into the future as you could need.
For each date, you have columns that contain information about that date, such as a LastDayOfMonth indicator, a DayOfWeek indicator, a FirstDayOfWeek indicator, WeekIndicator, etc. Populating a Kimball Date Dimension. I’m a big fan of the Kimball method of Data Warehousing.
A common task most of us setting up a new Data Warehouse face is creating a Date Dimension. In their book, “The Microsoft Data Warehouse Toolkit With SQL Server 2005 and the Microsoft Business Intelligence Toolset”, they have an example of a good date dimension table in their books sample code. My complaint though was not so much with the layout itself, I liked it and found it fairly complete. Instead it was the method they chose to load it. They used an Excel spreadsheet, then a SQL Server Integration Services package to read the Excel file and load the date dimension table.
To me this approach has a couple of drawbacks. A T-SQL solution was clearly, to me anyway, the superior answer for both ease of use and long term stability. A few things you should note. Their table also had an audit key, used presumably by the SSIS package. One final, but very important distinction. Create and Populate Date Dimension for Data Warehouse. Download script - 5.3 KB Introduction This article will especially help those people who work in Data warehouse and Business Intelligence.
Whenever as a starting point, they need to set New Data warehouse, during this time they need to create and fill their Date Dimension with various values of Date, Date Keys, Day Type, Day Name Of Week, Month, Month Name, Quarter, etc. Date and Time Dimensions Template-SSAS. Many times people are asking on the forums a common question: “what is the best way to make date/time dimensions?”.
We will try to answer this question by sharing our experience. We do not pretend our approach to be considered “the only correct” or “the best in the world”, we simply want to share how we do it. You may find the prepared SQL and AS databases here. Date Dimension In the SQL database you may find the following table: It contains 2 sets of fields: one for the attribute keys, the other – for names and translations.
The rest fields will be used for names of the attributes and for translation. The dimension looks like this: It has one natural Calendar hierarchy and a set of attributes as on the picture. Sample how dimension looks like: