Keith Rull

Here's an update to my blog entry 3 years ago regarding the same topic:

---Calculates the first day of the previous month
SELECT DATEADD(mm, DATEDIFF(m, 0, GETDATE()) - 1, 0) 
AS [First day of the previous month]

---Calculates the first day of current month
SELECT DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0) 
AS [First day of the current month]

---Calculates the first day of next month
SELECT DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0) 
AS [First day of the next month]

---Calculates the last day of the previous month
SELECT DATEADD(d, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)) 
AS [Last day of the previous month]

---Calculates the last day of the current month
SELECT DATEADD(d, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0)) 
AS [Last day of the current month]

--Calculates the last day of the next month
SELECT DATEADD(d, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 2, 0)) 
AS [Last day of the next month]

--Calculates the first day of the year
SELECT DATEADD(yy, DATEDIFF(yy,0,GetDate()), 0) 
AS [First day of the year]

--Calculates the first day of the quater
SELECT DATEADD(qq, DATEDIFF(qq,0,GetDate()), 0) 
AS [First day of the quarter]

--Calculates the first monday of the month
SELECT DATEADD(wk, DATEDIFF(wk,0,dateadd(dd, 6 - DATEPART(Day,GetDate()),GetDate())), 0) 
AS [First monday of the month]

--Calculates the last day of the prior month
SELECT DATEADD(mm, DATEDIFF(mm,0,GetDate()), 0) 
AS [Last day of the previous month]

--Calculates the last day of the prior year
SELECT DATEADD(yy, DATEDIFF(yy,0,GetDate()), 0)
AS [Last day of the previous year]

--Calculates the last day of the current year
SELECT DATEADD(mm, DATEDIFF(m,0,GetDate() ) + 1, 0)
AS [Last day of the current year]

--Calculates the monday of the current week
SELECT DATEADD(wk, DATEDIFF(wk,0,GetDate()), 0)
AS [Monday of the current week]

--Calculates the yesterdays date
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), -1)
AS [Yesterdays date]

--Calculates the todays date
SELECT GetDate()
AS [Todays date]

--Calculates the tommorows date
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 1)
AS [Tommorows date]

---Calculates the 15th day of previous month
SELECT DATEADD(d, 14, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) - 1 , 0))
AS [15th day of previous month]

---Calculates the 15th day of current month
SELECT DATEADD(d, 14, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))
AS [15th day of current month]

---Calculates the 15th day of next month
SELECT DATEADD(d, 14, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))
AS [15th day of next month]

--Gets the name of the current month
SELECT DATENAME(month, GetDate())
AS [Name of the current month]

--Gets the weekday name of the current date
SELECT DATENAME(dw, GetDate())
AS [Weekday name of the current date]

--Gets the weekday name of the current date next year
SELECT DATENAME(dw, DATEADD(yy, 1, GetDate()))
AS [Weekday name of the current date next year]

--Gets the weekday name of the current date last year
SELECT DATENAME(dw, DATEADD(yy, -1, GetDate()))
AS [Weekday name of the current date last year]

And here's the result for the query above

First day of the previous month
-------------------------------
2008-06-01 00:00:00.000

(1 row(s) affected)

First day of the current month
------------------------------
2008-07-01 00:00:00.000

(1 row(s) affected)

First day of the next month
---------------------------
2008-08-01 00:00:00.000

(1 row(s) affected)

Last day of the previous month
------------------------------
2008-06-30 00:00:00.000

(1 row(s) affected)

Last day of the current month
-----------------------------
2008-07-31 00:00:00.000

(1 row(s) affected)

Last day of the next month
--------------------------
2008-08-31 00:00:00.000

(1 row(s) affected)

First day of the year
-----------------------
2008-01-01 00:00:00.000

(1 row(s) affected)

First day of the quarter
------------------------
2008-07-01 00:00:00.000

(1 row(s) affected)

First monday of the month
-------------------------
2008-07-07 00:00:00.000

(1 row(s) affected)

Last day of the previous month
------------------------------
2008-07-01 00:00:00.000

(1 row(s) affected)

Last day of the previous year
-----------------------------
2008-01-01 00:00:00.000

(1 row(s) affected)

Last day of the current year
----------------------------
2008-08-01 00:00:00.000

(1 row(s) affected)

Monday of the current week
--------------------------
2008-07-21 00:00:00.000

(1 row(s) affected)

Yesterdays date
-----------------------
2008-07-23 00:00:00.000

(1 row(s) affected)

Todays date
-----------------------
2008-07-24 11:40:57.557

(1 row(s) affected)

Tommorows date
-----------------------
2008-07-25 00:00:00.000

(1 row(s) affected)

15th day of previous month
--------------------------
2008-06-15 00:00:00.000

(1 row(s) affected)

15th day of current month
-------------------------
2008-07-15 00:00:00.000

(1 row(s) affected)

15th day of next month
-----------------------
2008-08-15 00:00:00.000

(1 row(s) affected)

Name of the current month
------------------------------
July

(1 row(s) affected)

Weekday name of the current date
--------------------------------
Thursday

(1 row(s) affected)

Weekday name of the current date next year
------------------------------------------
Friday

(1 row(s) affected)

Weekday name of the current date last year
------------------------------------------
Tuesday

(1 row(s) affected)

I'm hoping that I could update this regularly. Did I miss anything? Post it on the comments and lets start an archive of useful sql date scripts.