I'm sharing my ElevateDB experiences with you as they are happening. So, I'm blogging about what I'm learning along the way. As I get more proficient with ElevateDB I hope to reflect that proficiency in my postings about ElevateDB.
Before I get to the Date Math stuff I need to tell you about the SQL query tool that comes with ElevateDB. It's called ElevateDB Manager. If you are familiar with Microsoft's SQL Server Management Studio or Microsoft's SQL Query Analyzer you should feel pretty comfortable with this tool. As of this writing it does not come with it's own manual. You can learn by doing or reading some of the support forum posts.
All the stuff I have been doing so far has been done from within a script window inside the the ElevateDB Manager. You open a new script window by clicking New|Script.
You can also set what are called "Breakpoints". Breakpoints do exactly what they sound like they do, they break in and stop execution of a script as a specific point. To set a break point within a script you click inside the grey margin next to the line where you want the script execution to stop.
Breakpoints are very cool. They let you see the current value of all the local variable that have been declared within the script at the very spot of the breakpoint. I set the breakpoint at the line
SET X = 1;
which is a do nothing statement purely for the purpose of letting me view the local variables.
I'm metioning these items because that is how I tested and viewed the results of the date math code I'm about to share with you.
How many times have you had to use a Start Date and End Date within your SQL queries? How many times have you had to set the Start Date equal to the first day of the current month and the End Date to the last day of the current month? How about the first and last days of the previous month, or the following month.
I'm about to show you a simple, sure fire way to set these dates so they work everytime, no matter what. You don't need to worry about going backwards or forwards a year. You don't need to worry whether there are 30 or 31 days in a month. You don't need to worry about February having 28 or 29 days. It's that simple.
Just copy and past this code into an ElevateDB Script. Set a breakpoint at the line mentioned above and your all set. There's only one line that needs to be inserted into the bottom two examples that makes them different from the first example. I've colored them red.
How to set the StartDate and EndDate for the Current Month
SCRIPT
BEGIN
----------------------------------------------------------------
-- This snippet of code sets the StartDate and EndDate to the
-- first day and last day of the current month
----------------------------------------------------------------
DECLARE Today DATE;
DECLARE YYYY INTEGER;
DECLARE MM INTEGER;
DECLARE DateStr VARCHAR(10);
DECLARE WorkDate DATE;
DECLARE StartDate DATE;
DECLARE EndDate DATE;
----------------------------------------------------------------
-- X is used set a breakpoint within ED Manager
----------------------------------------------------------------
DECLARE X INTEGER;
----------------------------------------------------------------
-- Get the current Year and Month
----------------------------------------------------------------
SET Today = Current_Date;
SET YYYY = EXTRACT(YEAR FROM Today);
SET MM = EXTRACT(Month FROM Today);
----------------------------------------------------------------
-- Set the WorkDate equal to the first day of the current month
-- by building a date string from the extracted date parts
-- Example: YYYY-MM-01
-- WHERE YYYY is the extracted Year
-- AND MM is the extracted Month
-- Make sure to pad any month less than 10 with a leading zero
----------------------------------------------------------------
SET DateStr = '';
SET DateStr = DateStr + CAST(YYYY as VARCHAR);
SET DateStr = DateStr + '-';
IF MM < 10 THEN
SET DateStr = DateStr + '0';
END IF;
SET DateStr = DateStr + CAST(MM as VARCHAR);
SET DateStr = DateStr + '-01';
SET WorkDate = CAST(DateStr as DATE);
----------------------------------------------------------------
-- Set StartDate equal to the first day of the month and create
-- the EndDate by performing two simple date math operations
-- 1. Add one Month to the WorkDate
-- 2. Subtract one Day from the WorkDate
----------------------------------------------------------------
SET StartDate = WorkDate;
SET WorkDate = WorkDate + Interval '1' MONTH;
SET WorkDate = WorkDate + Interval '-1' DAY;
SET EndDate = WorkDate;
SET X = 1;
END
How to set the StartDate and EndDate for the Previous Month
SCRIPT
BEGIN
----------------------------------------------------------------
-- This snippet of code sets the StartDate and EndDate to the
-- first day and last day of the previous month
----------------------------------------------------------------
DECLARE Today DATE;
DECLARE YYYY INTEGER;
DECLARE MM INTEGER;
DECLARE DateStr VARCHAR(10);
DECLARE WorkDate DATE;
DECLARE StartDate DATE;
DECLARE EndDate DATE;
----------------------------------------------------------------
-- X is used set a breakpoint within ED Manager
----------------------------------------------------------------
DECLARE X INTEGER;
----------------------------------------------------------------
-- Get the current Year and Month
----------------------------------------------------------------
SET Today = Current_Date;
SET YYYY = EXTRACT(YEAR FROM Today);
SET MM = EXTRACT(Month FROM Today);
----------------------------------------------------------------
-- Set the WorkDate equal to the first day of the current month
-- by building a date string from the extracted date parts
-- Example: YYYY-MM-01
-- WHERE YYYY is the extracted Year
-- AND MM is the extracted Month
-- Make sure to pad any month less than 10 with a leading zero
----------------------------------------------------------------
SET DateStr = '';
SET DateStr = DateStr + CAST(YYYY as VARCHAR);
SET DateStr = DateStr + '-';
IF MM < 10 THEN
SET DateStr = DateStr + '0';
END IF;
SET DateStr = DateStr + CAST(MM as VARCHAR);
SET DateStr = DateStr + '-01';
SET WorkDate = CAST(DateStr as DATE);
----------------------------------------------------------------
-- For Previous month subtract one month from WorkDate
-- Set StartDate equal to the first day of the month and create
-- the EndDate by performing two simple date math operations
-- 1. Add one Month to the WorkDate
-- 2. Subtract one Day from the WorkDate
----------------------------------------------------------------
SET WorkDate = WorkDate + Interval '-1' Month;
SET StartDate = WorkDate;
SET WorkDate = WorkDate + Interval '1' MONTH;
SET WorkDate = WorkDate + Interval '-1' DAY;
SET EndDate = WorkDate;
SET X = 1;
END
How to set the StartDate and EndDate for the Following Month
SCRIPT
BEGIN
----------------------------------------------------------------
-- This snippet of code sets the StartDate and EndDate to the
-- first day and last day of the following month
----------------------------------------------------------------
DECLARE Today DATE;
DECLARE YYYY INTEGER;
DECLARE MM INTEGER;
DECLARE DateStr VARCHAR(10);
DECLARE WorkDate DATE;
DECLARE StartDate DATE;
DECLARE EndDate DATE;
----------------------------------------------------------------
-- X is used set a breakpoint within ED Manager
----------------------------------------------------------------
DECLARE X INTEGER;
----------------------------------------------------------------
-- Get the current Year and Month
----------------------------------------------------------------
SET Today = Current_Date;
SET YYYY = EXTRACT(YEAR FROM Today);
SET MM = EXTRACT(Month FROM Today);
----------------------------------------------------------------
-- Set the WorkDate equal to the first day of the current month
-- by building a date string from the extracted date parts
-- Example: YYYY-MM-01
-- WHERE YYYY is the extracted Year
-- AND MM is the extracted Month
-- Make sure to pad any month less than 10 with a leading zero
----------------------------------------------------------------
SET DateStr = '';
SET DateStr = DateStr + CAST(YYYY as VARCHAR);
SET DateStr = DateStr + '-';
IF MM < 10 THEN
SET DateStr = DateStr + '0';
END IF;
SET DateStr = DateStr + CAST(MM as VARCHAR);
SET DateStr = DateStr + '-01';
SET WorkDate = CAST(DateStr as DATE);
----------------------------------------------------------------
-- For Following month add one month to WorkDate
-- Set StartDate equal to the first day of the month and create
-- the EndDate by performing two simple date math operations
-- 1. Add one Month to the WorkDate
-- 2. Subtract one Day from the WorkDate
----------------------------------------------------------------
SET WorkDate = WorkDate + Interval '1' Month;
SET StartDate = WorkDate;
SET WorkDate = WorkDate + Interval '1' MONTH;
SET WorkDate = WorkDate + Interval '-1' DAY;
SET EndDate = WorkDate;
SET X = 1;
END
Enjoy,
Gunny Mike
