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:

 

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 Add to Google  Subscribe to the Feed Subscribe to the Feed


About Daniel Johnson, Jr.

Daniel Johnson, Jr. believes in the power of connecting others and helping tell great stories with technology. Connect with Daniel on Google+.

Tags: , ,

Bad Behavior has blocked 1497 access attempts in the last 7 days.

Performance Optimization WordPress Plugins by W3 EDGE