Bradley M. Law

Sites with Date Formatting

http://www.sql-server-helper.com/tips/date-formats.aspx

http://www.blackwasp.co.uk/SQLDateTimeFormats.aspx

http://www.mssqltips.com/tip.asp?tip=1145

Other Date Formats

SELECT MONTH(CURRENT_TIMESTAMP);- Return a Month(6)

SELECT DAY(CURRENT_TIMESTAMP);-Return a Day:(22) 

SELECT DATE(CURRENT_TIMESTAMP); - returns a date (2004-06-22)

SELECT TIME(CURRENT_TIMESTAMP); - returns the time (10:33:11.840)

SELECT DAYOFWEEK(CURRENT_TIMESTAMP); - returns a numeric value (1-7)

SELECT DAYOFMONTH(CURRENT_TIMESTAMP); - returns a day of month (1-31)

SELECT DAYOFYEAR(CURRENT_TIMESTAMP); - returns the day of the year (1-365)

SELECT MONTHNAME(CURRENT_TIMESTAMP); - returns the month name (January - December)

SELECT DAYNAME(CURRENT_TIMESTAMP); - returns the name of the day (Sunday - Saturday)

SELECT WEEK(CURRENT_TIMESTAMP); - returns number of the week (1-53)

 Last 30 days
BETWEEN dateadd(month, -1, getdate()) and getdate()

This month
DateTime between  (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0)) AND (DATEADD(dd, DATEDIFF(dd,0, getdate()), 0))

Last Month
DateTime between  (DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0)) AND (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0))

Yesterday
DateTime BETWEEN (DATEADD(dd, DATEDIFF(dd,0, getdate()) -1, 0)) AND (DATEADD(dd, DATEDIFF(dd,0, getdate()) , 0)))