Special Date Functions in SQL, Part 1
For our internal dashboard work, we’re looking at metrics for the previous month. While I could create a SQL Server stored procedure to pass these dates from a user form, I really want to create a SQL view that will show last month’s information. That way, whenever the view is opened, it always shows data for last month.
I know in VB that there are FirstDayOfMonth and LastDayOfMonth functions, but I’ve been unable to find similar functions in SQL. In my research, I’ve found “Date and Time Manipulation in SQL Server 2000” helpful. I came up with this SQL:
SELECT
CAST(CONVERT(CHAR(10), DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101) AS smalldatetime) AS StartDate,
CAST(CONVERT(CHAR(10), DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101) AS smalldatetime) AS EndDate,
CAST(CONVERT(CHAR(10), DATEADD(dd,-(DAY(DATEADD(mm,-1,GETDATE()))-1),DATEADD(mm,-1,GETDATE())),101) AS smalldatetime) AS PrevMonthStartDate,
CAST(CONVERT(CHAR(10), DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),GETDATE()),101) AS smalldatetime) AS PrevMonthEndDate
It returns these results:
| StartDate | EndDate | PrevMonthStartDate | PrevMonthEndDate |
|---|---|---|---|
| 2008-08-01 00:00:00 | 2008-08-31 00:00:00 | 2008-07-01 00:00:00 | 2008-07-31 00:00:00 |
The SQL seems a bit convoluted, especially since I’m going to be using the start and end dates as criteria in a WHERE clause. So, I’m going to create a User-Defined Function (UDF) for each. I’ll share scripts for those in a follow-up post.
—–
Check out my other blogs:
Daniel Johnson, Jr.
Get That Job!
Journey Inside My Mind Blog
Journey Inside My Mind Podcast
QuotesBlog
Twitter.com/danieljohnsonjr
Connect on LinkedIn
Interesting Things I’ve Read
Related tags: daniel+johnson+jr sql+server
If you liked this post, click here to share it with your network!
Want to read more like this from Daniel Johnson, Jr.? If you do, subscribe right now!
Get via Email 
Add to Google 
Subscribe to the Feed
Tags: Business Intelligence, Dashboard, SQL
