August 20, 2011 at 3:55 am
Im using the below quiery to get the first day on the previous month. If I run it now I get the result shown.
SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))
2011-07-01 00:00:00.000
I want to run a select to get the Month and Year for the previous month. So in the case of the result above I need to do a query that will give me 2 seperate results, the month (07) and the year (2011). These two results would be the inserted into a table with columns called "Month" and "Year"
Any suggestions?
August 20, 2011 at 4:00 am
SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))
SELECT DATEPART(YEAR,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)))
SELECT DATEPART(MONTH,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)))
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 20, 2011 at 4:00 am
Hi Lance
You could use the DATEPART function to do that, but I'd suggest to do this kind of formatting in the front end application.
Greets
Flo
August 20, 2011 at 4:27 am
Perfect, works a treat.
Thanks.
August 20, 2011 at 10:28 am
lance.kentwell (8/20/2011)
Im using the below quiery to get the first day on the previous month. If I run it now I get the result shown.SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))
2011-07-01 00:00:00.000
I want to run a select to get the Month and Year for the previous month. So in the case of the result above I need to do a query that will give me 2 seperate results, the month (07) and the year (2011). These two results would be the inserted into a table with columns called "Month" and "Year"
Any suggestions?
You don't need to add a second to this...this produces the exact same result and is less code:
DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)
Adding 0 seconds is just another calculation that doesn't do anything except take more time to process.
If this is just to output the YEAR and MONTH in a query - you can use the built-in functions with the same name or DATEPART. Example:
YEAR(dateadd(month, datediff(month, 0, getdate()) - 1, 0))
MONTH(dateadd(month, datediff(month, 0, getdate()) - 1, 0))
Not sure why you need to insert this into a table - but, if you are inserting the first of the previous month you could save yourself some time and just create computed columns on that table based upon the column that contains the previous month.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 20, 2011 at 9:50 pm
lance.kentwell (8/20/2011)
These two results would be the inserted into a table with columns called "Month" and "Year"Any suggestions?
Yes, but not the one you want (which the others have already covered for you).
Unless you're populating a temporary table for reporting purposes or building a Calendar table, fight the requirement to store formatted temporal data in tables. It will lead to a world of hurt in too many ways for me to remember to list here.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 21, 2011 at 3:08 am
As it happens I am populating a table with prebuilt info for reporting purposes. It's much faster than forcing users to wait an hour each time they want to look at the same report.
August 21, 2011 at 9:09 am
lance.kentwell (8/21/2011)
As it happens I am populating a table with prebuilt info for reporting purposes. It's much faster than forcing users to wait an hour each time they want to look at the same report.
That's pretty close to the perfect answer. The thing that makes it a bit less than perfect is if it actually does taken an hour to render. A lot of reporting I've done involves several million rows but still returns some fairly complicated reports in 5 seconds or less. My personal SLA target is typically 1 second for such a thing but, in a pinch, I'll settle for 5 especially if SSRS is involved.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 21, 2011 at 6:09 pm
Jeff Moden (8/21/2011)
lance.kentwell (8/21/2011)
As it happens I am populating a table with prebuilt info for reporting purposes. It's much faster than forcing users to wait an hour each time they want to look at the same report.That's pretty close to the perfect answer. The thing that makes it a bit less than perfect is if it actually does taken an hour to render. A lot of reporting I've done involves several million rows but still returns some fairly complicated reports in 5 seconds or less. My personal SLA target is typically 1 second for such a thing but, in a pinch, I'll settle for 5 especially if SSRS is involved.
I wish it was that fast then I culd just run the query real time and get the same result. Perhaps you can shed some light on how to get a report to run that fast. Im using a vendor DB so I can make an alertatiosn to it. I took a query used in their standard reporting and im instering some values in the "Where" values to filter it. Woul dlove to have it run in just a few seconds. If I could i could build lots of really useful reports and get lots or praise from mgmnt so any help is greatly appreciated.
August 21, 2011 at 10:29 pm
Are you using plain T-SQL or Stored Procedures?
Do you have performance issues executing the SQL Code from SSMS? If not it is a SSRS issue.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 22, 2011 at 10:02 am
lance.kentwell (8/21/2011)
Jeff Moden (8/21/2011)
lance.kentwell (8/21/2011)
As it happens I am populating a table with prebuilt info for reporting purposes. It's much faster than forcing users to wait an hour each time they want to look at the same report.That's pretty close to the perfect answer. The thing that makes it a bit less than perfect is if it actually does taken an hour to render. A lot of reporting I've done involves several million rows but still returns some fairly complicated reports in 5 seconds or less. My personal SLA target is typically 1 second for such a thing but, in a pinch, I'll settle for 5 especially if SSRS is involved.
I wish it was that fast then I culd just run the query real time and get the same result. Perhaps you can shed some light on how to get a report to run that fast. Im using a vendor DB so I can make an alertatiosn to it. I took a query used in their standard reporting and im instering some values in the "Where" values to filter it. Woul dlove to have it run in just a few seconds. If I could i could build lots of really useful reports and get lots or praise from mgmnt so any help is greatly appreciated.
Without all of the details:
1. Resist the temptation to do "everything in one query". "Divide'n'Conquer" methods are frequently much more effective especially if more than about 6 tables are concerned.
2. Don't be afraid to use Temp Tables (NOT Table Variables) to hold interim results.
3. Do like I say in my signature line below... learn to think about what you want to do to a column rather than to a row.
4. Remember that "short code" isn't always fast code.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply