TSQL Date help
in [Server Configuration]
|
Prev: Error during pre-login handshake: SSL certificates and IIS?!
Next: Filegroup restore used to selectively refresh filegroup in 2nd db.
From: Kevin Nolan on 14 May 2010 11:20 SQL Server 2000 SP4 - Windows 2003 SP2. I have a query that creates a report for monthly enetered data in the database. It has to get all the data entered from and incluced the first day of the month and the last day of the month (Even if the first day or the last day of the month are Saturdays or sundays). I have the query starts with the following but how can I automate this so that I don't have to modify it every month manually ? Thanks declare @from_date as datetime, @to_date as datetime ----------------------------------------------------------------------------- -- Modify the from_date and to_date parameters before executing!!! ----------------------------------------------------------------------------- set @from_date = '5/1/2010' -- set to the first day of month -->> '5/1/2010' set @to_date = '6/1/2010' -- set to the first of the next month -->> '6/1/2010'
From: Erland Sommarskog on 14 May 2010 15:39 Kevin Nolan (KevinNolan(a)discussions.microsoft.com) writes: > SQL Server 2000 SP4 - Windows 2003 SP2. > > I have a query that creates a report for monthly enetered data in the > database. It has to get all the data entered from and incluced the first > day of the month and the last day of the month (Even if the first day or > the last day of the month are Saturdays or sundays). > > I have the query starts with the following but how can I automate this so > that I don't have to modify it every month manually ? First day of the current month: convert(char(6), getdate(), 112) + '01' Last day of the current month: dateadd(day, -1, dateadd(month, 1, convert(char(6), getdate(), 112) + '01')) Format 112 is YYYYMMDD. This format lends it very well, to do this type of manipulations together with dateadd. Note that for things like "last day", you often need to go to the first day of the next period, and then go one day back. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Ed Enstrom on 14 May 2010 15:39 declare @from_date as datetime, @to_date as datetime SELECT @from_date = '01-'+SUBSTRING('JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC',1+3*(datepart(mm,getdate())-1),3)+'-'+cast(datepart(yy,getdate()) as char(4)) SELECT @to_date = '01-'+SUBSTRING('JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDECJAN',1+3*(datepart(mm,getdate())),3)+'-'+cast(datepart(yy,getdate()) + datepart(mm,getdate())/12 as char(4)) Kevin Nolan wrote: > SQL Server 2000 SP4 - Windows 2003 SP2. > > I have a query that creates a report for monthly enetered data in the > database. It has to get all the data entered from and incluced the first day > of the month and the last day of the month (Even if the first day or the last > day of the month are Saturdays or sundays). > > I have the query starts with the following but how can I automate this so > that I don't have to modify it every month manually ? > > Thanks > > declare @from_date as datetime, @to_date as datetime > > ----------------------------------------------------------------------------- > -- Modify the from_date and to_date parameters before executing!!! > ----------------------------------------------------------------------------- > > set @from_date = '5/1/2010' -- set to the first day of month -->> '5/1/2010' > set @to_date = '6/1/2010' -- set to the first of the next month -->> > '6/1/2010' > >
From: John Bell on 14 May 2010 16:05 On Fri, 14 May 2010 08:20:05 -0700, Kevin Nolan <KevinNolan(a)discussions.microsoft.com> wrote: >SQL Server 2000 SP4 - Windows 2003 SP2. > >I have a query that creates a report for monthly enetered data in the >database. It has to get all the data entered from and incluced the first day >of the month and the last day of the month (Even if the first day or the last >day of the month are Saturdays or sundays). > >I have the query starts with the following but how can I automate this so >that I don't have to modify it every month manually ? > >Thanks > >declare @from_date as datetime, @to_date as datetime > >----------------------------------------------------------------------------- >-- Modify the from_date and to_date parameters before executing!!! >----------------------------------------------------------------------------- > >set @from_date = '5/1/2010' -- set to the first day of month -->> '5/1/2010' >set @to_date = '6/1/2010' -- set to the first of the next month -->> >'6/1/2010' > One way to do this is to use the dateadd function and day function (or datepart) e.g. SELECT DATEADD(month,-1,DATEADD(dd,1-DAY(GETDATE()),GETDATE())) AS [Start of Last Month], DATEADD(dd,-DAY(GETDATE()),GETDATE()) AS [End of Last Month] John
From: Jeffrey Williams on 14 May 2010 20:27
DATEADD(month, DATEDIFF(month, 0, getdate()), 0) -- first day of the month DATEADD(month, DATEDIFF(month, 0, getdate()) + 1, 0) -- first day of next month DATEADD(month, DATEDIFF(month, 0, getdate()) - 1, 0) -- first day of last month DATEADD(month, DATEDIFF(month, -1, getdate()), -1) -- last day of the month DATEADD(month, DATEDIFF(month, -1, getdate()) - 1, -1) -- last day of last month DATEADD(month, DATEDIFF(month, -1, getdate()) + 1, -1) -- last day of next month To use this in a query for last month: SELECT ... FROM ... WHERE dt_column >= dateadd(month, datediff(month, 0, getdate()) - 1, 0) -- first of last month AND dt_column < dateadd(month, datediff(month, 0, getdate()), 0) -- first of this month "John Bell" <jbellnewsposts(a)hotmail.com> wrote in message news:craru5dhn926urh3v7prd6n4b7l5sreqdp(a)4ax.com... > On Fri, 14 May 2010 08:20:05 -0700, Kevin Nolan > <KevinNolan(a)discussions.microsoft.com> wrote: > >>SQL Server 2000 SP4 - Windows 2003 SP2. >> >>I have a query that creates a report for monthly enetered data in the >>database. It has to get all the data entered from and incluced the first >>day >>of the month and the last day of the month (Even if the first day or the >>last >>day of the month are Saturdays or sundays). >> >>I have the query starts with the following but how can I automate this so >>that I don't have to modify it every month manually ? >> >>Thanks >> >>declare @from_date as datetime, @to_date as datetime >> >>----------------------------------------------------------------------------- >>-- Modify the from_date and to_date parameters before executing!!! >>----------------------------------------------------------------------------- >> >>set @from_date = '5/1/2010' -- set to the first day of month -->> >>'5/1/2010' >>set @to_date = '6/1/2010' -- set to the first of the next month -->> >>'6/1/2010' >> > > One way to do this is to use the dateadd function and day function > (or datepart) e.g. > > SELECT DATEADD(month,-1,DATEADD(dd,1-DAY(GETDATE()),GETDATE())) AS > [Start of Last Month], > DATEADD(dd,-DAY(GETDATE()),GETDATE()) AS [End of Last > Month] > > > John |