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 17 May 2010 08:49 Thank you all. I will try all of them and use one in my report query. Thanks again........ "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: Kevin Nolan on 17 May 2010 12:19 Thanks Erland but I needed the fist day and last day (All inclusive) of the last month since the query runs on the first day of the current month.......... "Erland Sommarskog" wrote: > 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: Kevin Nolan on 17 May 2010 12:23 Thanks John. Your query returns something like 2010-04-01 12:18:23.570 2010-04-30 12:18:23.570 How can I be sure that all data on the first day from 12:00 AM to 11:59 PM and the last day from 12:00 AM to 11:59 PM included ?. This will run on the first day (4:00 AM) of every month. Thanks. "John Bell" wrote: > 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: Kevin Nolan on 17 May 2010 12:45 Thanks Jeffrey. Your query is Ok except that it gives me incorrect result. I used DATEADD(month, DATEDIFF(month, 0, getdate()) - 1, 0) -- first day of last month DATEADD(month, DATEDIFF(month, -1, getdate()) - 1, -1) -- last day of last month but instead of getting 36 in the result, I got 33. It is not including the first day from 12:00 AM to 11:59 PM or the last day from 12:00 AM to 11:59 PM. Thanks again...... "Jeffrey Williams" wrote: > 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 >
From: Plamen Ratchev on 17 May 2010 13:10
The best is to use half-open interval including the first of last month and excluding the first of this month: WHERE date_column >= DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - 1, 0) AND date_column < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) -- Plamen Ratchev http://www.SQLStudio.com |