How can I get the first day of the month and the last day of the month just by selecting the month..... i.e. if I select month 3 will I be able to select 1 for the first day and then 31 for the last with just one query?
English is not my first language.....
You would have to know the year also to determine the last day of the month.
DECLARE @month varchar(2), @year varchar(4)
SET @month = '3'
SET @year = '2007'
SELECT FirstDay = CONVERT(datetime,@month+'/01/'+@year,101),
LastDay = DATEADD(day,-1,DATEADD(month,1,CONVERT(datetime,@month+'/01/'+@year,101)))
--Replace calls to GETDATE() with a variable/literal to find start and end of month
--for a different date.
--First day of month (subtract the day number, then add one day to get back to the 1st)
SELECT DATEADD(dy, 1, DATEADD(dy, -1 * DAY(GETDATE()), CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime)))
--Last day of month (add one month, then subtract the day number)
SELECT DATEADD(dy, -1 * DAY(DATEADD(mm, 1, GETDATE())), DATEADD(mm, 1, CAST(FLOOR(CAST(GETDATE() as float)) as datetime)))
You can read the article http://www.kodyaz.com/content/FirstAndLastDaysOfMonth.aspx about how to find the first day of a month and the last day of a month. You can also You can download the create scripts for the sql server user defined functions GetFirstDayOfMonth and GetLastDayOfMonth from http://www.kodyaz.com/files/21/sql_server_2000_user_defined_functions/entry335.aspx