Here’s a follow up to a previous post:

These lines of code will get you the First and Last day of the current year.

SQL:

SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),'First Day of the Year'
UNION
SELECT DATEADD(dd,-1,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))),'Last Day of the Year'


Results:
2008-01-01 00:00:00.000 First Day of the Year
2008-12-31 00:00:00.000 Last Day of the Year

I can’t give you a full tutorial on all things Dates when it comes to SQL, but I can show you how I solved a date task for a project I am now finishing up. This project includes finding data between these dates:

First Day of Last Month
Current Day of Last Month - so today’s day - 28 - but of last month
Last Day of Last Month

First Day of Month
Current Day of Month - Today
Last Day of Month

First Day of Next Month
Current Day of Next Month - same, today’s day of the month for next month
Last Day of Next Month

Oh, I also needed to get this month, for last year with the three same time frames. First day of the current month of last year, last day of current month of last year, and today’s day of last year.

First off, it’s relatively easy to search the interweb and use the first thing that pops up and gives you the answer. But when I tried that, the code was long, ugly, and just wasn’t to easy to maintain. So, I started with the most simple of all:


SELECT convert(varchar,DATEADD(YEAR,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,getdate()),0)),101),'First Day of This Month Last Year'
UNION
SELECT convert(varchar,DATEADD(YEAR,-1,DATEADD(MONTH,0,getdate())),101),'Today Last Year'
UNION
SELECT convert(varchar,DATEADD(YEAR,-1,DATEADD(MONTH,DATEDIFF(MONTH,-1,getdate()),-1)),101),'Last Day of This Month Last Year'
UNION
SELECT convert(varchar,DATEADD(MONTH,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,getdate()),0)),101),'First Day of Last Month'
UNION
SELECT convert(varchar,DATEADD(MONTH,-1,getdate()),101),'This Time Last Month'
UNION
SELECT convert(varchar,DATEADD(MONTH,DATEDIFF(MONTH,0,getdate()),-1),101),'Last Day of Last Month'
UNION
SELECT convert(varchar,DATEADD(MONTH,DATEDIFF(MONTH,0,getdate()),0),101),'First Day of This Month'
UNION
SELECT convert(varchar,getdate(),101),'Today'
UNION
SELECT convert(varchar,DATEADD(MONTH,DATEDIFF(MONTH,-1,getdate()),-1),101),'Last Day of This Month'
UNION
SELECT convert(varchar,DATEADD(MONTH,DATEDIFF(MONTH,-1,getdate()),0),101),'First Day of Next Month'
UNION
SELECT convert(varchar,DATEADD(MONTH,+1,getdate()),101),'This Time Next Month'
UNION
SELECT convert(varchar,DATEADD(MONTH,+1,DATEADD(MONTH,DATEDIFF(MONTH,-1,getdate()),-1)),101),'Last Day of Next Month'


Results:

01/01/2007 First Day of This Month Last Year
01/29/2007 Today Last Year
01/31/2007 Last Day of This Month Last Year
12/01/2007 First Day of Last Month
12/29/2007 This Time Last Month
12/31/2007 Last Day of Last Month
01/01/2008 First Day of This Month
01/29/2008 Today
01/31/2008 Last Day of This Month
02/01/2008 First Day of Next Month
02/29/2008 This Time Next Month
02/29/2008 Last Day of Next Month


I hope many will find this useful. Of course, you’ll most likely want to set variables with these…as your SQL could get very long and unreadable if you had to copy and paste these over and over for BETWEENs or some other similar date range.

** Please feel free to post…I’d like to know what others do, if there are more efficient ways, or newer ways. I am a relative Noob at SQL, so I know this can be done better.

Some people can master it. Some people know only enough to get a simple style set on an HTML element. Then there are some people that just don’t have the time, so they look to something that can help speed up the development process…just like most other programming languages. This brings up the notion of a Framework where the developer can just plug into an already built Style Sheet and make his/her changes as needed.

I’ve been compiling lists in my Firefox bookmarks for years, so now it’s time to place some of the most common (and not so common) in a list on here so that the many others can reference to.

1. Blueprint
2. YUI Grids CSS
3. YAML
4. Tripoli
5. Elements
6. WYMstyle
7. Schema
8. His CSS Framework
9. Cw/S Framework

This is a slim list so far as many people are not ready to release an official Framework classification for their CSS. I don’t, but I do use the same setup across most of my projects.

If you see one that is not on this list, please let me know and I’ll keep an updated and continuous list of these much like I have done with my preferred free software post. I’d love to hear what you all have to say about these as well.

I don’t really know what it is about these Church sites, but I am really really enjoying most of these designs. So classy and warm…and that’s most definitely the point. Well, it’s working for me! here’s another site for inspiration

Harbor Presbyterian Church

I promise next time I’ll look for something a little different and still inspiring. maybe the next one will be my design