I need to get all the dates present in the date range using SQL Server 2005
相关问题
- SQL join to get the cartesian product of 2 columns
- sql execution latency when assign to a variable
- Difference between Types.INTEGER and Types.NULL in
- What is the best way to cache a table from a (SQL)
- php PDO::FETCH_ASSOC doesnt detect select after ba
Here you go:
To generate a range of dates you could write a table-valued function. This is a function that creates a date dimension for a data warehouse - you could probably adapt it fairly readily by trimming out the specials.
Edit: Here it is without the date dimension hierarchy.
If what you want is to get all dates present in your database between two dates (i.e. what dates have customers placed orders in Q3 of 2008) you would write something like this:
If you have the dates in a table and simply want to select those between two dates you can use
If you want to produce the dates from nothing you could do it with a loop or you could populate a temporary table with dates and then select from that.
Here's Oracle version of date generation:
instead of all_objects it can be any table with enough rows to cover the required range.
Slightly more complicated but perhaps more flexible would be to make use of a table containing a sequential set of numbers. This allows for more than one date range with different intervals.
I actully use a variation of this to split dates into time slots (with various intervals but usually 5 mins long). My @numbers table contains a max of 288 since thats the total number of 5 min slots you can have in a 24 hour period.