Welcome to my online portfolio, the complement/substitute for my resume. The opinions included herein are my own and do not reflect those of any client or employer, past or present. Please check out the new site: http://danieljohnsonjr.com

Wednesday, August 20, 2008

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:

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:
2008-08-01 00:00:002008-08-31 00:00:002008-07-01 00:00:002008-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
Connect on LinkedIn
Interesting Things I've Read

Related tags:

No comments: