Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
funk.phenomena
Posting Yak Master
121 Posts |
 Posted - 2011-06-08 : 11:49:49
|
| Hi All - I have a database which reports on a date range (Start Date and end date). The time frame will always be the first of the month to the first day of the next month.DECLARE @StartDate DATETIMEDECLARE @EndDate DATETIMESET @StartDATE = '11/01/2010 00:00:000' -- UPDATE FOR EACH REPORTING MTHSET @EndDATE = '12/01/2010 00:01:00' -- UPDATE FOR EACH REPORTING MTHSELECT * FROM .... (and so forth)Question: Instead of always changing the date range, can you just declare a "reporting_month" variable? so that it would be like the following:SET @REPORTING_MONTH = 01SET @REPORTING_YEAR = 2011and the @startdate can be automatically set to the first of that month, and the @endate can be automatically set to the first of the following month at 1:00 hours?Thanks! |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-06-08 : 12:05:27
|
[code]select @StartDate = dateadd(month, @REPORTING_MONTH - 1, dateadd(year, @REPORTING_YEAR - 1900, 0))select @EndDate = dateadd(month, 1, @StartDate)[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-06-08 : 23:16:25
|
| Wrong approach; SQL is a data language, not a computational one. Build a table of report periods with the period name, the start and the end dates. This can be ported, optimized, etc.--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|
|
|
|
Start and End date Variables (Easy one!)