Dates

Now, by no means am I a SQL Guru. I know the basics. I seem to be having a
all kinds of problems fiquiring out how to properly use Dates in the
reporting tool. For example, How can I have a report give the user the
option to view the report by: year, month, day, date ranges, even by quarter
of a choosen year.
Can someone point me in the right direction?
Thanks,
SeanThis is your lucky day! :^)
I just completed this same thing. Here's a snippet of T-SQL to do that. I
have two variables, @.Begin_Date and @.End_Date that I use in my select to set
the date range for the report. In my Report I have text fields for Begin and
End date, but I also have a drop down that lets the user select "last 7 days"
Last 30 Days", Last Year, etc. If they select a value from the drop down, it
overrides the Begin Date text box. The dropdown sets integer values; 0 is
all periods, 1 is "Since Yesterday" etc. Here are the "allowable" values.
The ones marked ** are not supported by my code yet--I'll get around to them
later.
-- Allowable Values are:
--
-- 0 or blank All periods
-- 1 Today **
-- 2 Since beginning of Yesterday **
-- 3 Since beginning of This Week **
-- 4 Since beginning of Last Week **
-- 5 Within the last 7 days
-- 6 Since beginning of This Month **
-- 7 Since beginning of Last Month **
-- 8 Within the last 30 days
-- 9 Since beginning of This Year **
-- 10 Since beginning of Last Year **
-- 11 Within the last year
-- 12 Within the last 2 Years
-- ** Reserved - Not supported for COI yet.
You can add logic for This Quarter and such as well, using the same logic as
the code below. I seed some variables, and the goal of this SELECT is to set
@.Begin_Date and @.End_Date so that when I query for records, I can do a where
clause like:
WHERE
Form.Last_Modified_Date >= @.Begin_Date
AND
Form.Last_Modified_Date <= @.End_Date
Here's the code that sets @.Begin_Date and @.End_Date:
SELECT
-- Stamp the date and time
,@.TodayDateTime = GETDATE()
-- Get today as of 12:00am
,@.TodayDate = CAST (
CAST(DATEPART(year, @.TodayDateTime) AS varchar) +
'/' +
CAST(DATEPART(month, @.TodayDateTime) AS varchar)
+
'/' +
CAST(DATEPART(day, @.TodayDateTime) AS varchar)
AS datetime
)
-- Calculate the Begin_Date based on the period
,@.Begin_Date = CASE (@.Period)
WHEN 5 THEN DATEADD ( Day , -7, @.TodayDate)
WHEN 8 THEN DATEADD ( Day , -30, @.TodayDate)
WHEN 11 THEN DATEADD ( Year , -1, @.TodayDate)
WHEN 12 THEN DATEADD ( Year , -2, @.TodayDate)
ELSE CASE (SELECT ISDATE(@.Begin_Period))
WHEN 1 THEN CAST(@.Begin_Period AS datetime)
ELSE CAST('01/01/1900' AS datetime)
END
END
-- Dates count from 12:00AM, so we actually want End_Date at 12:00AM
the following day.
,@.End_Date = CASE (SELECT ISDATE(@.End_Period))
WHEN 1 THEN DATEADD ( Day , 1, CAST(@.End_Period AS datetime))
ELSE @.TodayDateTime
END
Regards,
Mike Sharp
"Sean" wrote:
> Now, by no means am I a SQL Guru. I know the basics. I seem to be having a
> all kinds of problems fiquiring out how to properly use Dates in the
> reporting tool. For example, How can I have a report give the user the
> option to view the report by: year, month, day, date ranges, even by quarter
> of a choosen year.
> Can someone point me in the right direction?
> Thanks,
> Sean|||rdcpro,
This looks excellent. How to implement it?
Example:
SELECT * FROM TESTDB
Where do I put all of your code? As a sperate Dataset?
Sorry for being stupid.
Sean
"rdcpro" wrote:
> This is your lucky day! :^)
> I just completed this same thing. Here's a snippet of T-SQL to do that. I
> have two variables, @.Begin_Date and @.End_Date that I use in my select to set
> the date range for the report. In my Report I have text fields for Begin and
> End date, but I also have a drop down that lets the user select "last 7 days"
> Last 30 Days", Last Year, etc. If they select a value from the drop down, it
> overrides the Begin Date text box. The dropdown sets integer values; 0 is
> all periods, 1 is "Since Yesterday" etc. Here are the "allowable" values.
> The ones marked ** are not supported by my code yet--I'll get around to them
> later.
> -- Allowable Values are:
> --
> -- 0 or blank All periods
> -- 1 Today **
> -- 2 Since beginning of Yesterday **
> -- 3 Since beginning of This Week **
> -- 4 Since beginning of Last Week **
> -- 5 Within the last 7 days
> -- 6 Since beginning of This Month **
> -- 7 Since beginning of Last Month **
> -- 8 Within the last 30 days
> -- 9 Since beginning of This Year **
> -- 10 Since beginning of Last Year **
> -- 11 Within the last year
> -- 12 Within the last 2 Years
> -- ** Reserved - Not supported for COI yet.
> You can add logic for This Quarter and such as well, using the same logic as
> the code below. I seed some variables, and the goal of this SELECT is to set
> @.Begin_Date and @.End_Date so that when I query for records, I can do a where
> clause like:
> WHERE
> Form.Last_Modified_Date >= @.Begin_Date
> AND
> Form.Last_Modified_Date <= @.End_Date
>
> Here's the code that sets @.Begin_Date and @.End_Date:
>
> SELECT
> -- Stamp the date and time
> ,@.TodayDateTime = GETDATE()
> -- Get today as of 12:00am
> ,@.TodayDate => CAST (
> CAST(DATEPART(year, @.TodayDateTime) AS varchar) +
> '/' +
> CAST(DATEPART(month, @.TodayDateTime) AS varchar)
> +
> '/' +
> CAST(DATEPART(day, @.TodayDateTime) AS varchar)
> AS datetime
> )
> -- Calculate the Begin_Date based on the period
> ,@.Begin_Date => CASE (@.Period)
> WHEN 5 THEN DATEADD ( Day , -7, @.TodayDate)
> WHEN 8 THEN DATEADD ( Day , -30, @.TodayDate)
> WHEN 11 THEN DATEADD ( Year , -1, @.TodayDate)
> WHEN 12 THEN DATEADD ( Year , -2, @.TodayDate)
> ELSE CASE (SELECT ISDATE(@.Begin_Period))
> WHEN 1 THEN CAST(@.Begin_Period AS datetime)
> ELSE CAST('01/01/1900' AS datetime)
> END
> END
> -- Dates count from 12:00AM, so we actually want End_Date at 12:00AM
> the following day.
> ,@.End_Date => CASE (SELECT ISDATE(@.End_Period))
> WHEN 1 THEN DATEADD ( Day , 1, CAST(@.End_Period AS datetime))
> ELSE @.TodayDateTime
> END
>
> Regards,
> Mike Sharp
>
> "Sean" wrote:
> > Now, by no means am I a SQL Guru. I know the basics. I seem to be having a
> > all kinds of problems fiquiring out how to properly use Dates in the
> > reporting tool. For example, How can I have a report give the user the
> > option to view the report by: year, month, day, date ranges, even by quarter
> > of a choosen year.
> >
> > Can someone point me in the right direction?
> >
> > Thanks,
> >
> > Sean