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: Erland Sommarskog on 17 May 2010 17:19 Kevin Nolan (KevinNolan(a)discussions.microsoft.com) writes: > 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.......... That was not clear from your post. However, with some amount of energy you would maybe figure out the correct solution from what I posted: >> 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. Now, in fact you want the first and last day for the previous month. All the building blocks are in the expressions above. As a hint: the last day of the previous month is the day before the first day of the current month. Sorry, for not actually posting the expressions, but somehow I hope that when I answer questions that people should also learn for the future! -- 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: Jeffrey Williams on 17 May 2010 23:00 You didn't read what I posted - I showed how to get the first of the month and the last day of the month. I then showed you how you should query using an open-interval. Instead of using the end of the month calculation - you should use the first of this month and the first of last month. What your query should end up doing is the same as: WHERE dt_column >= '20100401 00:00:00.000' AND dt_column < '20100501 00:00:00.000' This will include everything from last month. Now, just change out the hard-coded dates for the calculation to get the appropriate date. Jeff "Kevin Nolan" <KevinNolan(a)discussions.microsoft.com> wrote in message news:EABDCDA3-55C6-46E5-AC01-1F3C6CA655AC(a)microsoft.com... > 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, >> ) -- >> 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: Kevin Nolan on 18 May 2010 09:30
Thanks Plamen. I think that is the best way and it works..............Thanks again. "Plamen Ratchev" wrote: > 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 > . > |