Update: 11/19/2009

No!  Not that kind of fun with your date.  It seems lately a lot of Date conversion questions have been popping up.  So I'd thought I'd list them all here with their solutions and the author that has supplied them.  I will be adding to this list, everytime we come across other date manipulation requests.

1. If I only have the week and Year how do I displayJAN, FEB, MAR, ect Asked by Chandra78.

I suggested:

DECLARE @week int, @year int
SELECT @week = 23, @year = 2005

SELECT CONVERT(char(3)
 ,DATEADD(ww,@week-1
   ,CONVERT(datetime,'01/01/'+CONVERT(char(4),@Year)
   )
 ),100)

2. How Do I get the last day of the Month.  Was Asked by AskSQLTeam (as well as many others in the past)

Rob and Corey offer their suggestions as:

There's Just a slight type-o in Rob's post in the thread...he was missing the trailing parenthesis

DECLARE @datecol datetime
SELECT @datecol = '06/01/2005'
SELECT DateAdd(day,-1,DateAdd(month,DateDiff(month,0,@dateCol)+1,0))

Corey's Solution Appears to be a little simpler

SELECT dateadd(mm,1,@dateCol - day(@dateCol)+1)-1

3. How do I only Get the date portion of a datetime value

Alway the great question.  So much so that M$ decided to put it in SQL Server 2005 as 2 distinct datatypes.  Like every other RDBMS that I've ever worked with.  Unfortunatley it proved too much for the developers, and was pulled from the release.  Jeff, however discuss a work around to date and time isolationism in SQL Server in his blog.  After several thread back and forth (it sometimes is very difficult to asertain what the real question is) was "For example if I wanted to see all data for dates greater or equal to 01/01/2005“.  The short answer From Rob and madhivanan is:

SELECT * FROM myTable WHERE dateCol>='1/1/2005'

 But I suspect the real question to be the comparison of two dates and eliminating the time Componet.  While Jen's Answer with DATDIFF :

SELECT * FROM myTable WHERE datediff(day,@d1,@d2)=0

Does this, I believe it's a stage II (NonSargable) Predicate and will incur a scan.  The question usually is how do I see my Data between these 2 dates.  While this also shows an Index scan, it does mention that it may be a full scan, or only a range, which is what I believe it's doing.

USE Northwind
GO

DECLARE @datecol1 datetime, @datecol2 datetime
SELECT @datecol1 = '1996-09-01', @datecol2 = '1996-09-30'

SELECT *
  FROM Orders
 WHERE OrderDate > @datecol1 AND OrderDate < @datecol2

4. The Counting Days and Excluding Weekend and Holidays recurring question.  I think Nigel was the first time I saw a solution to this problem.  Nigel's Site, has some of the most invaluable stuff you will ever find.

5. Hours Till Margaritaville was a thing I messed around with that counted down the hours until 5:00 Friday.  Enigma (and his Sql Gladiators) however took it to a whole other level in his link.

6. Date of first day of week for current week was just asked recentley by steve_o  and was answered by thombpil.  He suggested:

select dateadd(dd, (datepart(dw, GetDate()) * -1) + 2, GetDate())

7. Win32 FILETIME values.  This is a must entry here.  Arnold came to the rescue that day Almost 2 years ago (man it's been 2 years?).  It's amazing what third party vendors will think up for storing data in SQL Server.  Just painful....I'm not sure if they just wanted to store the data that way, or they thought they were saving space.

SELECT CAST(((
    CAST(CAST(SUBSTRING(@d, 1, 2) AS int) AS float)  * 16777216.0
 +  CAST(CAST(SUBSTRING(@d, 3, 3) AS int) AS float)) * 16777216.0
 +  CAST(CAST(SUBSTRING(@d, 6, 3) AS int) AS float))/ 864000000000.0 - 109207.0 AS datetime)

8. Help: Date in field table and Select condition...well this was just plain silly...As Tara points out 

9. To get first and last day of a week as clboren asks, in their very first SQLTeam post.  Tara suggests the use of a numbers table (which, after many years doing sql I never even thought about before until reading about here at SQL Team) or Michael who employees a user defined function F_START_OF_WEEK.  Thanks again to Tara for pointing this out.  Since is was 6:30 EST, it was way past Margaritaville time (OK, it was on Thursday and I had to coach little league),  and I would have never seen it.

10.  Date as Int ...which seems to be, as Arnold point out as a “Wild guess: it's seconds from UNIX epoch.“  Seems to hold true.  We'll keep an eye out if lassew ever comes back to confirm this, BUT Arnolds Formula seems pretty well to hold true.

DECLARE @dok_dt int
SELECT @dok_dt = 1047034683
SELECT DATEADD(s, @dok_dt, '19700101 00:00:00')

11. Start of week Functions by Micheal

12. A very nice background about Dates can be found in this thread by Tibor Karaszi

13. Jeff Smith's very good article in his blog, Date Only and Time Only User Defined Dataypes in SQL Server 2000

14. How do I convert the current date in to a Julian Date?  Assuming ccyyddd

CREATE FUNCTION udf_Julian (
 @d datetime
)
RETURNS varchar(7)
  BEGIN
 RETURN (SELECT CONVERT(char(4),YEAR(@d))+CONVERT(varchar(3),DATEPART(dy,@d)))
  END

SELECT GetDate(), dbo.udf_Julian(GetDate())
GO

15.  How do I create a DB2 Date from SQL Server

Create Function SQLDateToDB2Date(@TargetDate datetime)
returns varchar(30)
as
begin
return replace(replace(convert(varchar(30), @TargetDate, 121),':', '.'), ' ', '.')+'000'
end

16. ...and how do I turn DB2 dates into SQL Server datetime

well you can't really, but you can fake it

DECLARE @x varchar(26)
SELECT @x = '2001-01-01-12:59:59:123456'
SELECT CONVERT(datetime,SUBSTRING(STUFF(@x,11,1,' '),1,23))

17. How Do I get the Last Friday of the Month

 Well, we've been hacking away in this thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=136027

But so far, this is the only Hack I could come up with

DECLARE @dateCol datetime
SELECT @dateCol = GetDate()

SELECT LastWeekDay
  FROM (
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-1 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-2 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-3 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-4 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-5 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-6 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-7 AS LastWeekDay
) AS XXX
WHERE DATENAME(WeekDay,LastWeekDay) = 'Friday'